bypass CDATA transformation

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

Lobrys - 28 May 2008 15:04 GMT

Hi,

You know that when you INSERT or UPDATE in a XML type field of table, SQL
Server deletes the CDATA et encode the section with HTML characters.

exemple :
CREATE TABLE #test ( xmldata xml )
INSERT INTO #test VALUES ('<test><![CDATA[test with & < >]]></test>')
SELECT xmldata FROM #test

It displays :
<test>test with & < ></test>

Question : It is possible to bypass that transformation?

Thanks by advance!
Sylvain

Reply to this Message

Martin Honnen - 29 May 2008 14:22 GMT

> You know that when you INSERT or UPDATE in a XML type field of table, SQL
> Server deletes the CDATA et encode the section with HTML characters.

"HTML characters"? What is that supposed to be?

> exemple :
> CREATE TABLE #test ( xmldata xml )
[quoted text clipped - 3 lines]
> It displays :
> <test>test with & < ></test>

&, <, and > are entity references to three of the five
entities the XML specification defines:
http://www.w3.org/TR/xml/#sec-predefined-ent.

> Question : It is possible to bypass that transformation?

I don't think so.

Signature

Martin Honnen --- MVP XML
http://JavaScript.FAQTs.com/

Reply to this Message

Michael Coles - 30 May 2008 05:39 GMT

In addition to losing CDATA section declarations SQL Server automatically
does some other manipulations on your XML when you store it in an xml
column:

- It strips the xml declaration processing instruction from the top, if
there is one
- It removes the DTD if you have one (you have to use CONVERT with option 2)
- Insignificant whitespace is not preserved

There are some more items listed here:
http://msdn.microsoft.com/en-us/library/ms187107.aspx

If you need to store an exact copy of your XML, including CDATA declarations
and nonentitized special characters, you need to store it using a varchar,
nvarchar, or varbinary data type.

Signature

========
Michael Coles
"Pro SQL Server 2008 XML"
http://www.amazon.com/Pro-SQL-Server-2008-XML/dp/1590599837/

> Hi,
>
[quoted text clipped - 13 lines]
> Thanks by advance!
> Sylvain

Reply to this Message

Lobrys - 30 Jun 2008 13:52 GMT

Thanks a lot for your answer!

Finaly, in order to not loose time, we decide to dont change anything in
database format. We keeped the xml type....
In theory, we have nothing to do. Conversion (sql-side and c#-side) is done
automaticly...
If we encounter problems in futur, we will try to find out.

bye!
Syl

> Hi,
>
[quoted text clipped - 13 lines]
> Thanks by advance!
> Sylvain

Reply to this Message