Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

creating XML from SQL query problem Expand / Collapse
Author
Message
Posted Thursday, May 9, 2013 6:35 AM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Wednesday, September 24, 2014 9:12 AM
Points: 24, Visits: 62
Hi All,
I have a problem trying add a element with an illegal character. I have the following SQL
SELECT  
'en-GB' AS '@xml:lang',
'20120217164611.1712._3FO0ZXYQ3@GBRAPPCHL1' AS '@payloadID',
'2012-02-17T16:46:11' AS '@timestamp',
'1.2.014' AS '@version',
( SELECT
( SELECT /* FROM */
(SELECT 'TecSolExchangeID' AS 'Credential/@domain', 33333 AS 'Credential/Identity'
FOR XML PATH(''), TYPE),
(SELECT 'name' AS 'Credential/@domain', 'Test Limited' AS 'Credential/Identity'
FOR XML PATH(''), TYPE)
FOR XML PATH('From'), TYPE ),
( SELECT /* TO */
(SELECT 'TecSolExchangeID' AS 'Credential/@domain', 66666 AS 'Credential/Identity'
FOR XML PATH(''), TYPE),
(SELECT 'name' AS 'Credential/@domain', 'Test Company' AS 'Credential/Identity'
FOR XML PATH(''), TYPE)
FOR XML PATH('To'), TYPE ),
( SELECT /* SENDER */
(SELECT 'TecSolExchangeID' AS 'Credential/@domain', 13041 AS 'Credential/Identity', '' AS 'Credential/SharedSecret'
FOR XML PATH(''), TYPE), ' cXML Link v1.3.0' AS 'UserAgent'
FOR XML PATH('Sender'), TYPE )
FROM TEST2.PORDER
WHERE POHNUM_0 = '2011302POH00000002'
FOR XML PATH('Header'),TYPE )
, ( SELECT 'production' AS '@deploymentMode',
( SELECT
( SELECT /* ShipNoticeHeader */
(SELECT ''
FOR XML PATH(''),
TYPE)
FOR XML PATH('ShipNoticeHeader'),
TYPE ),
( SELECT '' AS '@domain',
'' as 'CarrierIdentfier',
''AS 'ShipmentIdentifier'
FOR XML PATH('ShipControl'),
TYPE ),
(SELECT /* Ship Notice Portion */
(SELECT '1;29117/42' AS 'DocumentReference/@PayLoadID',
'' AS 'DocumentReference'
FOR XML PATH('OrderReference'),
TYPE),
(SELECT '4' AS '@Quantity',
'1' AS '@LineNo',
'Box 4' AS 'UnitOfMeasure'
FOR XML PATH('ShipNoticeItem'),
TYPE)
FOR XML PATH('ShipNoticePortion'),
TYPE)

FROM TEST2.PORDER
WHERE POHNUM_0 = '2011302POH00000002'
FOR XML PATH('ShipNoticeRequest'),
TYPE )
FROM TEST2.PORDER
WHERE POHNUM_0 = '2011302POH00000002'
FOR XML PATH('Request'),
TYPE)
FROM TEST2.PORDER
WHERE POHNUM_0 = '2011302POH00000002'
FOR XML PATH('?XML')

As you can see I have a question mark in the root field. This fails with the error.
'?XML' contains an invalid XML identifier as required by FOR XML; '?'(0x003F) is the first character at fault'.

My question is I need to be able to create the element with illegal characters in. For example I also need another element surrounding the message which will be "<!DOCTYPE>". same scenario except this field has a exclamation mark.

Is this possible first off, and if so, can anyone help me finish it off.

Thanks in advance
Post #1451077
Posted Friday, May 10, 2013 9:41 AM


Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: Yesterday @ 2:51 PM
Points: 1,061, Visits: 2,577
mark.dungey 56406 (5/9/2013)
Hi All,
I have a problem trying add a element with an illegal character. I have the following SQL
SELECT  
'en-GB' AS '@xml:lang',
'20120217164611.1712._3FO0ZXYQ3@GBRAPPCHL1' AS '@payloadID',
'2012-02-17T16:46:11' AS '@timestamp',
'1.2.014' AS '@version',
( SELECT
( SELECT /* FROM */
(SELECT 'TecSolExchangeID' AS 'Credential/@domain', 33333 AS 'Credential/Identity'
FOR XML PATH(''), TYPE),
(SELECT 'name' AS 'Credential/@domain', 'Test Limited' AS 'Credential/Identity'
FOR XML PATH(''), TYPE)
FOR XML PATH('From'), TYPE ),
( SELECT /* TO */
(SELECT 'TecSolExchangeID' AS 'Credential/@domain', 66666 AS 'Credential/Identity'
FOR XML PATH(''), TYPE),
(SELECT 'name' AS 'Credential/@domain', 'Test Company' AS 'Credential/Identity'
FOR XML PATH(''), TYPE)
FOR XML PATH('To'), TYPE ),
( SELECT /* SENDER */
(SELECT 'TecSolExchangeID' AS 'Credential/@domain', 13041 AS 'Credential/Identity', '' AS 'Credential/SharedSecret'
FOR XML PATH(''), TYPE), ' cXML Link v1.3.0' AS 'UserAgent'
FOR XML PATH('Sender'), TYPE )
FROM TEST2.PORDER
WHERE POHNUM_0 = '2011302POH00000002'
FOR XML PATH('Header'),TYPE )
, ( SELECT 'production' AS '@deploymentMode',
( SELECT
( SELECT /* ShipNoticeHeader */
(SELECT ''
FOR XML PATH(''),
TYPE)
FOR XML PATH('ShipNoticeHeader'),
TYPE ),
( SELECT '' AS '@domain',
'' as 'CarrierIdentfier',
''AS 'ShipmentIdentifier'
FOR XML PATH('ShipControl'),
TYPE ),
(SELECT /* Ship Notice Portion */
(SELECT '1;29117/42' AS 'DocumentReference/@PayLoadID',
'' AS 'DocumentReference'
FOR XML PATH('OrderReference'),
TYPE),
(SELECT '4' AS '@Quantity',
'1' AS '@LineNo',
'Box 4' AS 'UnitOfMeasure'
FOR XML PATH('ShipNoticeItem'),
TYPE)
FOR XML PATH('ShipNoticePortion'),
TYPE)

FROM TEST2.PORDER
WHERE POHNUM_0 = '2011302POH00000002'
FOR XML PATH('ShipNoticeRequest'),
TYPE )
FROM TEST2.PORDER
WHERE POHNUM_0 = '2011302POH00000002'
FOR XML PATH('Request'),
TYPE)
FROM TEST2.PORDER
WHERE POHNUM_0 = '2011302POH00000002'
FOR XML PATH('?XML')

As you can see I have a question mark in the root field. This fails with the error.
'?XML' contains an invalid XML identifier as required by FOR XML; '?'(0x003F) is the first character at fault'.

My question is I need to be able to create the element with illegal characters in. For example I also need another element surrounding the message which will be "<!DOCTYPE>". same scenario except this field has a exclamation mark.

Is this possible first off, and if so, can anyone help me finish it off.

Thanks in advance


As you know, the question mark character (?) and the exclamation point character (!) are not allowed in XML tags per the XML specification, so FOR XML will not let you specify tags that contain invalid characters.

However, you can specify 'XML' as the "row wrapper" tag in your outer FOR XML PATH, then use the REPLACE function to change it to '?XML' and the STUFF function to add the "<!DOCTYPE>" declaration. SQL Server will implicitly convert the XML to varchar to apply the REPLACE function, so your output will be varchar rather than XML, which only makes sense because the SQL Server xml datatype can only hold well-formed XML.

Here's an example:

WITH CTE AS (

SELECT 1 AS ID
,'QUE' AS COL

UNION ALL

SELECT 2 AS ID
,'TAL' AS COL

)

SELECT STUFF(REPLACE((SELECT ID, COL FROM CTE FOR XML PATH ('XML')), 'XML', '?XML'), 1, 0, '<!DOCTYPE>') AS RESULT



Jason Wolfkill
Blog: SQLSouth
Twitter: @SQLSouth
Post #1451650
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse