none
How to get [CDATA] with FOR XML PATH?

    Question

  • How do I get a CDATA section in results when using a SELECT ... FOR XML PATH?  (SQL 2005 SP1)

    Thanks.

    Wednesday, August 09, 2006 2:15 PM

Answers

  • Our client insists on the CDATA sections.  I had to change the query from FOR XML PATH to FOR XML EXPLICIT and then use the CDATA directive.
    Tuesday, September 05, 2006 2:06 AM
  • The XML datatype does not preserve CDATA sections... it is part of the W3C XQuery recommendation that it does not preserve CDATA section.

     

    Again, why do you need the CDATA section? There is no semantic difference in the data that you store. Could you please provide me with a clear use case that shows the need for generating CDATA sections?

     

    Thanks

    Michael - MSFT

    Wednesday, March 28, 2007 6:56 AM

All replies

  • You cannot get CDATA sections... CDATA sections are only useful when you handauthor data and have no semantic meaning and are not being preserved by the XML data type either.

    Could you please provide me with the reasons why you want to generate them?

    Thanks

    Michael

    Sunday, September 03, 2006 3:14 AM
  • Our client insists on the CDATA sections.  I had to change the query from FOR XML PATH to FOR XML EXPLICIT and then use the CDATA directive.
    Tuesday, September 05, 2006 2:06 AM
  • I'm struggling with this myself... You never know what your uses throw into a DB.

     

    I had a 3000 line for xml explicit query that I converted to use xml path.  Using xml path I have created a query that is only 30 lines, but that doesn't matter if i can't get the data back with cdata nodes.  I've got to use the bloated query.

     

    Has anyone solved this madness with a work around?

     

    This seems to be a common question posted by many.  Why  MS wouldn't create a cdata() method is beyond me!

    Monday, March 26, 2007 10:05 PM
  • The XML datatype does not preserve CDATA sections... it is part of the W3C XQuery recommendation that it does not preserve CDATA section.

     

    Again, why do you need the CDATA section? There is no semantic difference in the data that you store. Could you please provide me with a clear use case that shows the need for generating CDATA sections?

     

    Thanks

    Michael - MSFT

    Wednesday, March 28, 2007 6:56 AM
  • Here is one.

    I am a controls engineer and I am using a C# program to fill a sql database with code.  I am then writing a query to build a xml file from this table which will help me auto-write a majority of my program code.  If I can't write CDATA, I can't auto fill my comments which would be dissapointing.
    Sunday, March 15, 2009 2:58 PM
  • Dear MRys,

    In the current implementation of ObamaCare XML is a major part of the Standard of exchanging information between providers.  Document attachments are part of the specification.  Things like DiCom Images, PDF's, and Scanned Documents.  

    With current method that SQL Server is handling CDATA a 3 megabyte Dicom Image that is embedded in a CCD document is being processed for entities and it should not the over head related to the byte for byte IO and the fact that I want the maintain the true integrity of the data I have to base64encode the data and store it that way just seems redundant and overboard if the specification was adhered to.  And CDATA was let alone and intact in the XML Data object in SQL Server.

    While I just recently stumble on this issue through some testing on a new Database model very XML concentric , it frustrates me to see that the Standard is not supported.  And forces me to separate components of the original XML outside of SQL Server creates a overly complicated solution to storing a document that is valid XML into multiple tables and multiple columns just to prevent this behavior.

    If there is anyway we can get an option on the insert to not parse CDATA that would be awsome or just support the standard as it is written.

    Thanks,

    Ron 


    Friday, February 22, 2013 11:00 PM