SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


creating XML from SQL query problem


creating XML from SQL query problem

Author
Message
mark.dungey 56406
mark.dungey 56406
Valued Member
Valued Member (56 reputation)Valued Member (56 reputation)Valued Member (56 reputation)Valued Member (56 reputation)Valued Member (56 reputation)Valued Member (56 reputation)Valued Member (56 reputation)Valued Member (56 reputation)

Group: General Forum Members
Points: 56 Visits: 108
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
wolfkillj
wolfkillj
SSCrazy
SSCrazy (2.7K reputation)SSCrazy (2.7K reputation)SSCrazy (2.7K reputation)SSCrazy (2.7K reputation)SSCrazy (2.7K reputation)SSCrazy (2.7K reputation)SSCrazy (2.7K reputation)SSCrazy (2.7K reputation)

Group: General Forum Members
Points: 2656 Visits: 2582
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
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search