SQL Server——以XML数据类型存储换行符

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

是否可以在XML数据类型中存储非字母数字字符(更具体地说是换行字符)?

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?

我的实际问题是将值存储在一个表中(而不是本地变量)，所以可能在我的表中对应的XML列有一些设置来完成任务?

3 个解决方案

#1

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

这是不可能的。XML数据类型存储为XML DOM树，而不是字符串。

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

如果想要保留空格，则必须将其存储为varchar。

#2

My answer in the XSLT context should apply here:

我在XSLT上下文中的回答应该适用于这里:

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).

XML解析实体通常存储在计算机文件中，为方便编辑，这些文件被组织成行。这些行通常由一些字符(#xD)和行提要(#xA)的组合分隔。

So this might be what you are looking for:

所以这可能就是你想要的:

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

#3

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:

根据XML规范，XML标记内部的空白不被认为是重要的，并且不会被保留。然而，元素外部的空白将:

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>

#1