How to make a SQL query not delete the HTML data returned in the query

Question

I have the following SQL query ....

select AanID as '@name', '<![CDATA[' + Answer + ']]>' from AuditAnswers for XML PATH('str'), ROOT('root') 

which works wonderfully, but there may sometimes be HTML markup in the Response column. The request automatically escapes this HTML from the Response column in the generated XML. I do not want it. I will wrap this resulting column on CDATA, so no escaping is required.

I want the result to be like this ...

 <str name="2"><![CDATA[<DIV><DIV Style="width:55%;float:left;">Indsfgsdfg]]></str> 

instead of this...

 <str name="2">&lt;![CDATA[&lt;DIV&gt;&lt;DIV Style="width:55%;float:left;"&gt;In</str> 

Is there a function or other mechanism for this?

Answer 1

You can use the for xml explicit directive and cdata :

 select 1 as tag, null as parent, AanID as [str!1!name], Answer as [str!1!!cdata] from AuditAnswers for xml explicit 

Answer 2

Choosing "FOR XML" excludes any existing XML so that it does not violate the consistency of XmlDocument. The first line of the example you gave is considered malformed XML and cannot be loaded by the XmlDocument object, as well as by most parsers. I would think about restructuring what you are trying to do in order to have a more effective solution.

Answer 3

You can specify that the output should be treated as CDATA when using EXPLICIT XML queries. Cm:

Using EXPLICIT Mode

and

Example: specifying the CDATA Directive

Answer 4

What is the use of having <[CDATA[ <div></div> ]]> compared to <div></div> in your database? It seems to me that in both cases, you have the right HTML snippet in your XML representation, and reading it with a decent XML parser should give you an unprocessed original version in both cases.