CODEWITHSUNDEEP
sql-serversqlxml
fahadash
fahadash

Reputation: 3281

SQL Server XML DataType butchers my CDATA Section

I have a table with one column with XML datatype.

CREATE TABLE Employee
(
  ID INT,
  Name VARCHAR(200),
  ExtraInfo XML
)

I must use the XML data type for ExtraInfo column, and I have the following INSERT statement

INSERT INTO Employee
VALUES 
(1, 'John Doe', 
CONVERT(XML, '<root><section><![CDATA[Some weird content with weird characters (*&#^@$]]></section></root>'))

After the above statement is executed, I end up with the following in the ExtraInfo column.

<root>
  <section>Some weird content with weird characters (*&amp;#^@$</section>
</root>

How do I keep my CDATA section intact?

I have already checked the other related question, that is not quite what I am looking for.

Upvotes: 1

Views: 295

Answers (1)

Mikael Eriksson
Mikael Eriksson

Reputation: 138960

I was actually looking for something from Microsoft stating that the XML data type does not support CDATA sections.

Here it is: How to get [CDATA] with FOR XML PATH?

Quote from Michael Rys who was responsible for most of the XML stuff in SQL Server at Microsoft:

The XML datatype does not preserve CDATA sections... it is part of the W3C XQuery recommendation that it does not preserve CDATA section.

Upvotes: 1

Related Questions