how to stop Select...for xml explicit from escaping inner (CDATA) xml

• gbritton1

  SSCertifiable

  Points: 6522

  More actions

  June 3, 2014 at 12:38 pm

  #294074

  I'm working on a script to produce XML. There are two parts to the XML: an envelope (called "message") and a payload ("payload). Here's an example of what the result should look like:

  <?xml version="1.0" encoding="utf-8" ?>

  <message guid="cb9f7927-a4c7-44f8-9e55-bd5dd3e85894">

  <client name="BNS CASL Client" guid="3dc500f3-dffb-455f-a071-12c4fa37a1eb" endPoint="http://localhost:51873/CASLWS.aspx" />

  <payload>

  <![CDATA[

  <?xml version="1.0" encoding="utf-8" ?>

  <payload>

  <email>

  <emailAddress>ted@adventures.com</emailAddress>

  <legalEntity>Bill and Ted's Excellent Adventures</legalEntity>

  <businessCategory>Time Travel</businessCategory>

  <CID>123</CID>

  <name>Ted</name>

  <postalCode>M1N 1M1</postalCode>

  <eventDate>2014-06-03T12:00:00-05:00</eventDate>

  </email>

  </payload>

  ]]>

  </payload>

  </message>

  Here's what I have so far, using FOR XML EXPLICIT. It's close to what I need except for the inner XML inside the CDATA tag. The inner XML is getting escaped by the query which is not what I need.

  Query so far (without the full inner XML -- baby steps!):

  select 1 as Tag

  , 0 as Parent

  , 'cb9f7927-a4c7-44f8-9e55-bd5dd3e85894' as [message!1!guid]

  , NULL as [client!2!name]

  , NULL as [client!2!guid]

  , NULL as [client!2!endPoint]

  , NULL as [payload!3!]

  union all

  select 2 as tag

  , 1 as parent

  , NULL

  , 'BNS CASL Client' as [client!2!name]

  , '3dc500f3-dffb-455f-a071-12c4fa37a1eb' as [client!2!guid]

  , 'http://localhost:51873/CASLWS.aspx' as [client!2!endPoint]

  , NULL

  union all

  select 3 as tag

  , 1 as parent

  , NULL

  , NULL

  , NULL

  , NULL

  , '<foo>bar</foo>' as [payload!3!!CDATA]

  for xml explicit

  which produces this result:

  <message guid="cb9f7927-a4c7-44f8-9e55-bd5dd3e85894">

  <client name="BNS CASL Client" guid="3dc500f3-dffb-455f-a071-12c4fa37a1eb" endPoint="http://localhost:51873/CASLWS.aspx" />

  <payload>& lt;foo>bar& lt;/foo& gt;</payload& gt;

  </message>

  (Note that I had to add a space between the '&' and 'lt' and 'gt' to get it to post in the forum without the forum converting them back to < and >!)

  You can see that SQL escaped the < and >. Also the CDATA escape sequence is missing.

  I want to see < instead of & lt and > instead of & gt. Also, I need the CDATA escape sequence.

  How can I do that?

• gbritton1

  SSCertifiable

  Points: 6522

  More actions

  June 3, 2014 at 12:57 pm

  #1718642

  Here's some more on this problem:

  on MSDN,

  there is this example:

  USE AdventureWorks2012;

  GO

  SELECT 1 as Tag,

  0 as Parent,

  ProductModelID as [ProductModel!1!ProdModelID],

  Name as [ProductModel!1!Name],

  '<Summary>This is summary description</Summary>'

  as [ProductModel!1!!CDATA] -- no attribute name so ELEMENT assumed

  FROM Production.ProductModel

  WHERE ProductModelID=19

  FOR XML EXPLICIT

  This works great! however, if I modifiy it to put the CDATA in a nested element, I lose the CDATA and see the < and > escaped, e.g.

  USE AdventureWorks2012;

  GO

  SELECT 1 as Tag,

  0 as Parent,

  ProductModelID as [ProductModel!1!ProdModelID],

  Name as [ProductModel!1!Name],

  null as [summary!2!]

  FROM Production.ProductModel

  WHERE ProductModelID=19

  union all

  SELECT 2 as Tag,

  1 as Parent,

  null,

  null,

  '<Summary>This is summary description</Summary>'

  as [summary!2!!CDATA] -- no attribute name so ELEMENT assumed

  FROM Production.ProductModel

  WHERE ProductModelID=19

  FOR XML EXPLICIT

  Run this and see the summary element. CDATA is gone and < and > are escaped.

• Eirikur Eiriksson

  SSC Guru

  Points: 182912

  More actions

  June 3, 2014 at 1:04 pm

  #1718644

  Quick thought, think FOR XML PATH with TYPE is better suited for this than EXPLICIT.

  😎

• mister.magoo

  SSC-Forever

  Points: 47174

  More actions

  June 3, 2014 at 1:14 pm

  #1718652

  There doesn't seem to be any non-static data in the Wrapper XML, so can't you just use strings to build that with the Payload XML inside?

  MM
  

  ---

  
  select geometry::STGeomFromWKB(0x0106000000020000000103000000010000000B0000001000000000000840000000000000003DD8CCCCCCCCCC0840000000000000003DD8CCCCCCCCCC08408014AE47E17AFC3F040000000000104000CDCCCCCCCCEC3F9C999999999913408014AE47E17AFC3F9C99999999991340000000000000003D0000000000001440000000000000003D000000000000144000000000000000400400000000001040000000000000F03F100000000000084000000000000000401000000000000840000000000000003D0103000000010000000B000000000000000000143D000000000000003D009E99999999B93F000000000000003D009E99999999B93F8014AE47E17AFC3F400000000000F03F00CDCCCCCCCCEC3FA06666666666FE3F8014AE47E17AFC3FA06666666666FE3F000000000000003D1800000000000040000000000000003D18000000000000400000000000000040400000000000F03F000000000000F03F000000000000143D0000000000000040000000000000143D000000000000003D, 0);

• Forum Etiquette: How to post Reporting Services problems
[/url]
• Forum Etiquette: How to post data/code on a forum to get the best help - by Jeff Moden
[/url]
• How to Post Performance Problems - by Gail Shaw
[/url]