Azure SQL DB中如何转换XML内的低ASCII编码字符

我的XML字符串里包含EDI常用的低阶ASCII特殊字符——char(28)（文件分隔符）、char(29)（组分隔符）、char(30)（记录分隔符），这些字符被编码成这类十六进制实体格式（比如对应char(28)）。原本用CLR把这些编码转成原始字符后存入表中（只存XML里的数据字段，不存XML本身），但现在要在不支持CLR的Azure SQL DB里实现这个功能。

我拿不准最优方案是直接用XQuery提取并转成正确格式，还是先用XQuery的value方法提取文本，再用T-SQL做后续处理（比如嵌套REPLACE、结合CROSS/OUTER APPLY的REPLACE，甚至STUFF——TRANSLATE因为是多字符替换没法用）。查资料发现其他XQuery版本支持fn:replace或bin:decode-string，但SQL Server的XQuery不支持这些方法，我正试着用replace value of这类方式实现。

示例代码 #

DECLARE @xml_edi XML  = '<Bundle><RawData>ABCDE&#x1E;&#x1C;FGHIJK&#x1D;LMNOP</RawData></Bundle>' 
SELECT x.y.value('./RawData[1]','varchar(max)')
from @xml_edi.nodes('/Bundle')x(y)

（注：原示例中的是HTML转义后的结果，实际XML里的实体是）

预期结果 #

ABCDEFGHIJKLMNOP --注：受浏览器/编辑器限制，三个特殊字符显示无差异
（实际为 ABCDE + char(30) + char(28) + FGHIJK + char(29) + LMNOP）

可行解决方案 #

方案1：提取后用T-SQL嵌套REPLACE处理 #

这是最直接的方式，先提取文本，再依次替换每个编码实体：

DECLARE @xml_edi XML  = '<Bundle><RawData>ABCDE&#x1E;&#x1C;FGHIJK&#x1D;LMNOP</RawData></Bundle>' 

SELECT 
    REPLACE(
        REPLACE(
            REPLACE(
                x.y.value('./RawData[1]','varchar(max)'),
                '&#x1C;', CHAR(28)
            ),
            '&#x1D;', CHAR(29)
        ),
        '&#x1E;', CHAR(30)
    ) AS DecodedRawData
FROM @xml_edi.nodes('/Bundle')x(y)

方案2：结合CROSS APPLY优化嵌套REPLACE可读性 #

如果要替换的实体较多，用CROSS APPLY分步替换能提升代码可读性：

DECLARE @xml_edi XML  = '<Bundle><RawData>ABCDE&#x1E;&#x1C;FGHIJK&#x1D;LMNOP</RawData></Bundle>' 

SELECT final.DecodedRawData
FROM @xml_edi.nodes('/Bundle')x(y)
CROSS APPLY (SELECT x.y.value('./RawData[1]','varchar(max)') AS TempData) initial
CROSS APPLY (SELECT REPLACE(initial.TempData, '&#x1C;', CHAR(28)) AS TempData) step1
CROSS APPLY (SELECT REPLACE(step1.TempData, '&#x1D;', CHAR(29)) AS TempData) step2
CROSS APPLY (SELECT REPLACE(step2.TempData, '&#x1E;', CHAR(30)) AS TempData) final

方案3：XQuery自定义函数实现转换（SQL Server 2016+） #

SQL Server的XQuery支持自定义函数，可以创建一个函数来解析十六进制实体：

CREATE FUNCTION dbo.DecodeEdiEntities(@input XML)
RETURNS VARCHAR(MAX)
AS
BEGIN
    DECLARE @decoded VARCHAR(MAX)
    SET @decoded = @input.query('
        declare function local:decode-entity($str as xs:string) as xs:string {
            let $entities := (
                ("", string-from-code-point(28)),
                ("", string-from-code-point(29)),
                ("", string-from-code-point(30))
            )
            return foldl(
                ($acc, $pair) => replace($acc, $pair[1], $pair[2]),
                $str,
                $entities
            )
        };
        local:decode-entity(/Bundle/RawData/text())
    ').value('.', 'varchar(max)')
    RETURN @decoded
END
GO

-- 使用示例
DECLARE @xml_edi XML  = '<Bundle><RawData>ABCDE&#x1E;&#x1C;FGHIJK&#x1D;LMNOP</RawData></Bundle>' 
SELECT dbo.DecodeEdiEntities(@xml_edi) AS DecodedRawData

性能对比 #

• 少量实体替换时，嵌套REPLACE性能最优，操作简单直接。
• 替换实体较多时，CROSS APPLY可读性更好，性能和嵌套REPLACE接近。
• XQuery自定义函数性能略低于前两种，但适合需要在XML查询逻辑内完成转换的场景。

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