Clever Geek Handbook

SQL Server - Saving Strings in XML Data Format

Is it possible to store non-alphanumeric characters (more specifically, line break characters) in XML data format?

The code below illustrates my problem:

declare @a xml
declare @b nvarchar(max)

set @b = '<Entry Attrib="1'+CHAR(13)+'2" />'

print @b

set @a=convert(xml,@b,1)

set @b=convert(nvarchar, @a,1)

print @b

Output:

<Entry Attrib="1
2" />
<Entry Attrib="1 2"/>

Is there a way to keep the string consistency?

My actual problem is to store the value in the table (not a local variable), so maybe there is some setting for the corresponding XML column in my table that would do the job?

+2


source to share


3 answers


It would be impossible. The XML data type is stored as an XML DOM tree, not a string.



Instead, you need to store it as varchar if you want to keep spaces.

+2


source


My answer in the context of XSLT should apply here:

XML-parsed objects are often stored in computer files, which are organized in lines for editing convenience. These lines are usually separated by some combination of carriage return (#xD) and linear feed (#xA) characters.



So this might be what you are looking for:

set @b = '<Entry Attrib="1&#xA;2" />'
+2


source


White space within an XML tag is not considered meaningful by the XML specification and is not preserved. White space outside the element would be:

declare @a xml
declare @b nvarchar(max)

set @b = '<Entry Attrib="12"> fo'+CHAR(13)+'o</Entry>'
print @b
set @a=convert(xml,@b,1)
set @b=convert(nvarchar(max), @a,1)
print @b

will output:

<Entry Attrib="12"> fo
o</Entry>
<Entry Attrib="12"> fo
o</Entry>
0


source






More articles:


All Articles