Hi,

I inserted data with whitespace in XML column and the space is
stored as entitized string "& # x 2 0 ;".
MSSQL seems to entitize some characters during serialization. Some
characters are &, <, >, white-space, TAB, linefeed, carriage return, and
surrogate pairs and so on.

I found the document which says that if we want to avoid entitization, we
can use the explicit CONVERT option 1 when casting from xml to a string or
binary type.

(there is one space in front of the xml document)
insert into kmtest values(convert(xml,' <a><b></b></a>',0)); <== strip
whitespace
insert into kmtest values(convert(xml,' <a><b></b></a>',1)); <== preserve
whitespace

select CONVERT(VARBINARY(max), c1) from kmtest;
-----------------------------------------------------------------------------
0xFFFE3C0061003E003C0062002F003E003C002F0061003E00
0xFFFE260023007800320030003B003C0061003E003C006200 2F003E003C002F0061003E00

select CONVERT(VARBINARY(max), c1, 1) from kmtest;
-----------------------------------------------------------------------------
0xFFFE3C0061003E003C0062002F003E003C002F0061003E00
0xFFFE20003C0061003E003C0062002F003E003C002F006100 3E00

Is there any other way to avoild entitized string? I think it may cause some
performance problem when we need to convert whole XML document into string or
binary data types... I think XQuery method query() can be used to retrieve
the data, but I would like to select the whole document incuding elements...

Thank you,
KM

If you only want to select the whole document then don't store it in an XML
column. The XML datatype stores the document in binary format which is
optimized for quick parsing. If you're not planning to parse the document
then storing it in parsed form is a waste of time and resources. XML
parsers are required to ignore non-significant white space so if you want
all spaces preserved they have to be entitized.

--
This posting is provided "AS IS" with no warranties, and confers no rights.
Use of included script samples are subject to the terms specified at
http://www.microsoft.com/info/cpyright.htm

"KM" <KM (AT) discussions (DOT) microsoft.com> wrote