0

Trying to export some data from SQL Server to an XML file. However when casting a VARCHAR to XML data type CDATA section gets removed.
How can I keep the CDATA?

Example:

SELECT CAST('<a><b>foo</b><c><![CDATA[<html><body>bar</body></html>]]></c></a>' AS xml)

Returns:

<a>
  <b>foo</b>
  <c>&lt;html&gt;&lt;body&gt;bar&lt;/body&gt;&lt;/html&gt;</c>
</a>
user3274635
  • 101
  • 3
  • `CDATA` in XML is an encoding choice, when re-encoded a different choice can be made (eg. `bar` does not need any encoding, so using `CDATA` just makes the output longer. `CDATA` isn't represented in the [XML Information Set](https://www.w3.org/TR/2004/REC-xml-infoset-20040204/). – Richard Jan 31 '19 at 13:18
  • Perhaps if you expanded the question to explain *why* you want to preserve the `CDATA` sections? – Richard Jan 31 '19 at 13:19
  • The only way to do that is by not casting it to `XML` in the first place. If you have a requirement that data be round-tripped exactly (for compatibility with some broken decoder) you'll have to muddle along with character data, or else use client libraries that care more about syntactical preservation. – Jeroen Mostert Jan 31 '19 at 13:24
  • If you just need the end result to have `CDATA`, and don't care about the intermediate XML, you can work around things by using explicit markers (like ``/``) and replacing these later. But this is not trivial: the element contents will also need to be unescaped, otherwise characters like `<` and `>` will end up as their escape sequences. And, of course, you can't know where the CDATA sections were in the original once you've cast it to `XML`. – Jeroen Mostert Jan 31 '19 at 13:31

0 Answers0