How to get SQL query to not escape HTML data returned in 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 the column 'Answer' can sometimes have HTML markup in it. The query automatically escapes this HTML from the 'Answer' column in the generated XML. I don't want that. I will be wrapping this resulting column in CDATA so the escaping is not necessary.

I want the result to be 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 to do this?

Answer 1

What would be the benefit of having <[CDATA[ <div></div> ]]> over having <div></div> in your database output? To me, it looks like you would have a properly escaped HTML fragment in your XML output in both cases, and reading it back with a decent XML parser should give you the unescaped original version in both cases.

Answer 2

Selecting anything "FOR XML" escapes any pre-existing XML so that it will not break the consistency of the XmlDocument. The first example line you gave is considered to be improperly formed XML, and will not be able to be loaded by an XmlDocument object, as well as most parsers. I would consider restructuring what you're trying to do so that you can have a more efficient solution.

Answer 3

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

Using EXPLICIT Mode

and

Example: Specifying the CDATA Directive

Answer 4

You can use for xml explicit and the cdata directive:

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