May 9, 2013 at 6:35 am
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
May 10, 2013 at 9:41 am
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
Viewing 2 posts - 1 through 1 (of 1 total)
You must be logged in to reply to this topic. Login to reply