如何在SQL Server中解析包含特殊非法字符的XML字符串

解决方案 #

核心问题是原始字符串不属于合法XML格式（XML规范不允许属性值内出现未转义的<），而你之前的方案是将整个原始XML字符串当做普通文本做XML转义，导致XML标签本身的<、>也被转义，最终得到的是一个包含转义后XML文本的XML值，不是结构化的XML节点，因此无法查询到对应节点。

你需要定向转义属性值内部的特殊字符，不修改XML标签本身的标记字符，具体方案如下：

方案1：SQL Server 2017及以上版本（使用内置正则替换）

-- 原始不规范XML字符串
declare @xml_string varchar(max) = '<root><result value="3 < 5" /></root>'

-- 先替换属性值内的&，避免后续转义字符被二次处理
set @xml_string = REGEXP_REPLACE(@xml_string, '="([^"]*)"', 
  '="'+ 
  REPLACE(
    REPLACE(
      REPLACE(REPLACE('$1', '&', '&amp;'), '<', '&lt;'), 
    '>', '&gt;'),
  '"', '&quot;')
  +'"'
)

-- 转成合法XML类型
declare @xml xml = @xml_string

-- 正常查询属性值
select                               
R.N.value('@value[1]', 'nvarchar(max)') [value]               
from @xml.nodes('/root/result') as R(N)

执行后可以得到属性值3 < 5，最终XML结构符合预期。

方案2：低版本SQL Server（固定XML结构场景）

如果你的XML结构固定，仅需要处理value属性的转义，可以用字符串截取替换的通用方案：

declare @xml_string varchar(max) = '<root><result value="3 < 5" /></root>'

-- 提取value属性值
declare @attr_start int = CHARINDEX('value="', @xml_string) + 7
declare @attr_end int = CHARINDEX('"', @xml_string, @attr_start)
declare @attr_value varchar(max) = SUBSTRING(@xml_string, @attr_start, @attr_end - @attr_start)

-- 转义属性值内的特殊字符
set @attr_value = REPLACE(REPLACE(REPLACE(@attr_value, '&', '&amp;'), '<', '&lt;'), '>', '&gt;')

-- 替换回原字符串
set @xml_string = STUFF(@xml_string, @attr_start, @attr_end - @attr_start, @attr_value)

-- 转XML查询
declare @xml xml = @xml_string
select R.N.value('@value[1]', 'nvarchar(max)') [value] from @xml.nodes('/root/result') as R(N)

内容的提问来源于stack exchange，提问作者user3551865