SQL Server - 以 XML 数据类型存储换行符 [英] SQL Server - Storing linebreaks in XML data type

问题描述

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

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

输出为:

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

有什么办法可以保持换行符完好无损吗?

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

我的实际问题是将值存储在表中(而不是局部变量)，所以也许我的表中相应的 XML 列有一些设置可以完成这项工作?

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?

推荐答案

我的 XSLT 上下文中的答案 应适用于此处:

My answer in the XSLT context should apply here:

XML 解析的实体经常被存储在计算机文件中，用于编辑方便，被组织成行.这些行通常由字符的某种组合回车 (#xD) 和换行(#xA).

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

这可能就是您要找的:

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

这篇关于SQL Server - 以 XML 数据类型存储换行符的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！