SQL Server使用XML拆分字符串报非法名称字符错误如何解决

原有函数通过拼接字符串转XML的方式实现拆分，未对XML规范中的特殊保留字符做转义处理。XML语法中&、<、>、"、'属于特殊实体字符，不能直接出现在XML节点内容中，其中&是实体引用的起始标记，未转义时会被XML解析器判定为非法字符，直接抛出你遇到的解析错误。

在拼接XML字符串前，先对输入字符串中的5类XML特殊字符按顺序转义（注意&必须第一个转义，避免后续转义生成的&被重复处理），再执行原有的分隔符替换、XML解析逻辑即可。XML节点取值的.value()方法会自动将转义后的实体字符还原为原始字符，不会影响最终拆分结果的正确性。
修正后的自定义函数代码如下：

ALTER FUNCTION [dbo].[splitString](@input Varchar(max), @Splitter Varchar(99)) 
RETURNS TABLE AS
RETURN
    SELECT Split.a.value('.', 'NVARCHAR(max)') AS Data 
    FROM (
        SELECT CAST (
            '<M>' 
            + REPLACE(
                REPLACE(
                    REPLACE(
                        REPLACE(
                            REPLACE(@input, '&', '&amp;'), -- &必须放在第一个替换
                            '<', '&lt;'
                        ),
                        '>', '&gt;'
                    ),
                    '"', '&quot;'
                ),
                '''', '&apos;'
            ), @Splitter, '</M><M>') 
            + '</M>' AS XML
        ) AS Data 
    ) AS A 
CROSS APPLY Data.nodes ('/M') AS Split(a);

用之前触发报错的字符串测试：

SELECT Data FROM [dbo].[splitString](
    'Front Office,Food & Beverage,Housekeeping,Human Resource & Training,Reservation,Other',
    ','
)

执行后不会报XML解析错误，可正确返回拆分结果：

• Front Office
• Food & Beverage
• Housekeeping
• Human Resource & Training
• Reservation
• Other

补充说明：如果使用场景中输入字符串可能包含XML不支持的不可见控制字符，可在转义逻辑前增加一层控制字符过滤，常规业务场景下上述转义逻辑已经可以覆盖绝大多数特殊字符兼容需求。

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