转换时转义XML特殊字符

Question

我使用csv拆分器来满足我的需求。

你可以直接抓取并按原样运行：

declare @t table(data varchar(max))
insert into @t select 'a,b,c,d'
insert into @t select 'e,,,h'
;with cte(xm) as 
(
select convert(xml,'<f><e>' + replace(data,',', '</e><e>') + '</e></f>') as xm 
from @t
)
select
xm.value('/f[1]/e[1]','varchar(32)'),
xm.value('/f[1]/e[2]','varchar(32)'),
xm.value('/f[1]/e[3]','varchar(32)'),
xm.value('/f[1]/e[4]','varchar(32)')
from cte

唯一的问题是，如果我在数据中引入一个XML敏感字符，比如&：

insert into @t select 'i,j,&,k'

它失败并出现错误：字符24，非法字符

一种解决方案是替换&字符到&amp在飞行中，如下图所示：

select convert(xml,'<f><e>' + replace(replace(data,'&','&amp'),',', '</e><e>') + '</e></f>') as xm 

但是有几十个特殊的XML字符，我需要在转换时转义，而且我不能在其中嵌套几十个replace(replace(…函数。我就是这么做的，这很混乱。

如何修改上述代码以转义XML敏感字符，并产生相同的结果？

谢谢！

Answer 1

你已经得到了马丁·史密斯的答案。但我认为，在这里为追随者们提供一个答案是值得的。想要提供一些解释和进一步的信息，rextester链接可能在未来无法访问。。。

如果你想到这样一个表中的字符串。。。

DECLARE @mockup TABLE(SomeXMLstring VARCHAR(100));
INSERT INTO @mockup VALUES('This is a string with forbidden characters like "<", ">" or "&"');

--。。。您可以很容易地添加XML标记：

SELECT '<root>' + SomeXMLstring + '</root>'
FROM @mockup ;

--结果看起来像XML

<root>This is a string with forbidden characters like "<", ">" or "&"</root>

--但事实并非如此！你可以测试一下，CAST( AS XML)将失败：

SELECT CAST('<root>This is a string with forbidden characters like "<", ">" or "&"</root>' AS XML);

--有时，人们试图进行自己的替换，并开始用相应的实体<, > and &替换<, > and &。但这将需要大量的更换，以确保的安全。

--但XML正在为我们隐式地完成这一切

SELECT SomeXMLstring 
FROM @mockup
FOR XML PATH('')

--这是的结果

<SomeXMLstring>This is a string with forbidden characters like "&lt;", "&gt;" or "&amp;"</SomeXMLstring>

--有趣的是：我们可以很容易地用AS [*]:创建一个无名元素

SELECT SomeXMLstring AS [*]
FROM @mockup
FOR XML PATH('')

--结果是一样的，但没有标签：

This is a string with forbidden characters like "<", ">" or "&"

--尽管这看起来像SSMS中的XML，但当用作字符串时，它将隐式地转换为NVARCHAR(MAX)。

--您可以将其用于字符串的隐式转义，只要您觉得需要构建具有字符串串联的XML：

SELECT CAST('<root>' + (SELECT SomeXMLstring AS [*] FOR XML PATH('')) + '</root>' AS XML)
FROM @mockup ;

最终回答您的问题

这行必须使用技巧：

select convert(xml,'<f><e>' + replace((SELECT data AS [*] FOR XML PATH('')),',', '</e><e>') + '</e></f>') as xm