1

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?

Improve this question

4 Answers 4

Reset to default
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.

Improve this answer
Sign up to request clarification or add additional context in comments.

1 Comment

Good to know looks like explicit mode is what I'm looking for as a few others have mentioned. Thanks for the info.
1

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
Improve this answer

1 Comment

Interesting. I was not aware of this. This looks like it'll do the trick for me.
1

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

Improve this answer

1 Comment

Great thanks for the links. I didn't know about explicit mode.
0

What would be the benefit of having <[CDATA[ <div></div> ]]> over having &lt;div&gt;&lt;/div&gt; 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.

Improve this answer

1 Comment

I don't know that there is any benefit or if it matters at all. There was some other work I had done where embedded markup between CDATA tags was already using '<' and '>' so I wanted to remove &lt; and &gt; text just to be consistent with that previous work but you're right ultimately I don't think it will matter.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.