|
| Hi,
I inserted data with whitespace in XML column and the space is
stored as entitized string " ".
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.
[color=darkred]
(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(ma
x), c1) from kmtest;
-----------------------------------------------------------------------------
0xFFFE3C0061003E003C
0062002F003E003C002F
0061003E00
0xFFFE26002300780032
0030003B003C0061003E
003C0062002F003E003C
002F0061003E00
select CONVERT(VARBINARY(ma
x), c1, 1) from kmtest;
-----------------------------------------------------------------------------
0xFFFE3C0061003E003C
0062002F003E003C002F
0061003E00
0xFFFE20003C0061003E
003C0062002F003E003C
002F0061003E00
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
|
|