views:

333

answers:

3

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

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

The output is:

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

Is there any way I could keep the line break intact?

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

+2  A: 

It would not be possible. The XML Data type is stored as an XML DOM Tree, not a string.

You would have to store it as a varchar instead if you want to keep whitespace.

Erich
Could you point me to some Microsoft official documentation regarding this?
jhrecife
A: 

White space inside of an XML tag is not considered significant according to the XML specification, and will not be preserved. White space outside of the element will however:

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>
Jeremy Seghi
I get the following error for the second "convert":Msg 6354, Level 16, State 10, Line 13Target string size is too small to represent the XML instance
jhrecife
I got that the first time through too. SQL Server stores nvarchar as 2 bytes for each characters entered + 2 bytes. Your script sets the storage of @b to 46 bytes (2*22 characters + 2), while mine sets the storage at 66 bytes (2*32 characters + 2). It seems the optimizer will keep that initial assignment storage allocation in the query plan, so you'll need to use a different variable name, run mine in a new query window.
Jeremy Seghi
A: 

My answer in the XSLT context should apply here:

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

So this might be what you are looking for:

set @b = '<Entry Attrib="1&#xA;2" />'
rasx
I'm using SQL Server 2005 and I'm getting the following output:<Entry Attrib="12" /><Entry Attrib="12"/>
jhrecife