SQL SERVER xml with CDATA
I have a table in my database with a column containing xml. The column type is nvarchar(max). The xml is formed in this way
<root> <child>....</child> . . <special> <event><![CDATA[text->text]]></event> <event><![CDATA[text->text]]></event> ... </special> </root>
I have not created the db, I cannot change the way information is stored in it but I can retrieve it with a select. For the extraction I use
select cast(replace(xml,’utf-8′,’utf-16′)as xml)
It works well except for cdata, whose content in the query output is: text -> text
Is there a way to retrieve also the CDATA tags?
One Solution collect form web for “SQL SERVER xml with CDATA”
Well, this is – as far as I know – not possible on normal ways…
CDATA section has one sole reason: include invalid characters within XML for lazy people…
CDATA is not seen as needed at all and therefore is not really supported by normal XML methods. Or in other words: It is supported in the way, that the content is properly escaped. There is no difference between correctly escaped content and not-escaped content within
CDATA actually! (Okay, there are some minor differences like including
]]> within a
CDATA-section and some more tiny specialties…)
The big question is: Why?
What are you trying to do with this afterwards?
Try this. the included text is given as is:
DECLARE @xml XML = '<root> <special> <event><![CDATA[text->text]]></event> <event><![CDATA[text->text]]></event> </special> </root>' SELECT t.c.query('text()') FROM @xml.nodes('/root/special/event') t(c);
So: Please explain some more details: What do you really want?
If your really need nothing more than the wrapping
CDATA you might use this:
SELECT '<![CDATA[' + t.c.value('.','varchar(max)') + ']]>' FROM @xml.nodes('/root/special/event') t(c);
Update: Same with outdated
I just tried how the outdated approach with
FROM OPENXML handles this and found, that there is absolutely no indication in the resultset, that the given text was within a
CDATA section originally. The “Some value here” is exactly returned in the same way as the text within
DECLARE @doc XML = '<root> <child>Some value here </child> <special> <event><![CDATA[text->text]]></event> <event><![CDATA[text->text]]></event> </special> </root>'; DECLARE @hnd INT; EXEC sp_xml_preparedocument @hnd OUTPUT, @doc; SELECT * FROM OPENXML (@hnd, '/root',0); EXEC sp_xml_removedocument @hnd;