如何在SQL Server中处理含未转义双引号的XML解析问题

核心问题 #

你拿到的是不合法的XML字符串：属性值中的双引号未转义（如value="gfjsdgfdjy duahsd "absdjsd""），导致SQL Server无法正常解析。SQL Server的XML类型会严格验证格式合法性，所以必须先修复XML字符串，再进行解析。

可行解决方案 #

假设原始数据以字符串类型（NVARCHAR(MAX)）存储，以下是具体修复+解析步骤：

1. 预处理XML字符串，转义属性内的未转义双引号 #

通过循环替换，将属性值内部的双引号转义为XML合法的"，避免破坏XML结构：

DECLARE @xmlStr NVARCHAR(MAX) = N'<root>
    <field id="1" value="gfjsdgfdjy duahsd "absdjsd"" />
    <field id="37" value="ysgfdyua" />
    <field id="13" value="asdas" />
    <field id="73" value="fgdgfd" />
    <field id="adsf" value="fdsa" />
</root>';

-- 循环替换属性值内的未转义双引号
WHILE PATINDEX(N'%="[^"]%"%', @xmlStr) > 0
BEGIN
    SET @xmlStr = STUFF(
        @xmlStr,
        PATINDEX(N'%="[^"]%"%', @xmlStr) + CHARINDEX(N'"', @xmlStr, PATINDEX(N'%="[^"]%"%', @xmlStr)+2) - 1,
        1,
        N'&quot;'
    );
END

2. 转换为合法XML并解析 #

将修复后的字符串转为XML类型，再执行原有的提取逻辑：

DECLARE @fixedXml XML = TRY_CAST(@xmlStr AS XML);
DECLARE @tmp TABLE (field NVARCHAR(100), val NVARCHAR(200));

IF @fixedXml IS NOT NULL
BEGIN
    INSERT INTO @tmp (field, val) 
    SELECT 
        field.value('@id', 'nvarchar(100)') AS fieldID,
        field.value('@value', 'nvarchar(200)') AS val
    FROM @fixedXml.nodes('root/field') A(field);
END

-- 查看结果
SELECT * FROM @tmp;

补充说明 #

• 如果数据源本身是SQL Server的XML类型，不可能存在这种无效数据（存储时会自动验证格式），所以你的数据源大概率是字符串类型。
• 循环替换逻辑会处理所有属性值内的未转义双引号，若存在多个连续未转义引号，循环会逐一处理直到无匹配模式。

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