Xml Cdata

[Alpicus]

Hi,

When I execute the following script on SQL Server 2005 SP2 :

IF EXISTS (select * from sys.objects where name = 'TEXTEST2' and type_desc
= 'USER_TABLE')
BEGIN
DROP TABLE TEST.TEXTEST2
END
CREATE TABLE TEST.TEXTEST2
(
ARR XML
);
INSERT INTO TEST.TEXTEST2 (arr) VALUES ('<?xml version=''1.0''
encoding=''wind ows-1252''?><E0><E0 1 LT=''C''><![CDATA[L''échéancier &
env.ASP]]></E01><E02 LT=''C''><![CDATA["Tools" -->
"Terminkalender "]]></E02></E0>');

The result in the XML field (ARR) is :
<E0><E01 LT="C">L'échéan cier & env.ASP</E01><E02 LT="C">"Tools" -->
"Terminkalender "</E02></E0>

Why the CDATA section is gone ?

Thanks in advance.

[Motoma]

Hi,

When I execute the following script on SQL Server 2005 SP2 :

IF EXISTS (select * from sys.objects where name = 'TEXTEST2' and type_desc
= 'USER_TABLE')
BEGIN
DROP TABLE TEST.TEXTEST2
END
CREATE TABLE TEST.TEXTEST2
(
ARR XML
);
INSERT INTO TEST.TEXTEST2 (arr) VALUES ('<?xml version=''1.0''
encoding=''wind ows-1252''?><E0><E0 1 LT=''C''><![CDATA[L''échéancier &
env.ASP]]></E01><E02 LT=''C''><![CDATA["Tools" -->
"Terminkalender "]]></E02></E0>');

The result in the XML field (ARR) is :
<E0><E01 LT="C">L'échéan cier & env.ASP</E01><E02 LT="C">"Tools" -->
"Terminkalender "</E02></E0>

Why the CDATA section is gone ?

Thanks in advance.

For that field, you don't actually need CDATA there. I would assume that the XML data type doesn't actually store strings, rather, it keeps data arranged in a hierarchical structure, and returns the dataset as an XML string.

[Alpicus]

For that field, you don't actually need CDATA there. I would assume that the XML data type doesn't actually store strings, rather, it keeps data arranged in a hierarchical structure, and returns the dataset as an XML string.

Hi Motoma,

The objective of XML CDATA section is to keep the contents without any changes (Check the doc. : http://www.w3.org/TR/xml/#sec-cdata-sect). In my sample, the "&" will be transformed to "&amp;" and I don't want this when I get the data from the DB.

Is there a way to have an XML field keeping the CDATA contents or do I have to switch to VARBINARY (MAX) or TEXT fields because my objective is to keep the contents unchanged ?

TIA.

[Motoma]

Are you saying that when you do a select out of that field, the & is not preserved?