SQL Server表导出CSV时处理特殊字符及非法XML字符问题

问题根源 #

你遇到的Msg 9420错误，是因为字段中包含XML规范不允许的非法字符（就是显示为�的字符），而你的自定义函数dbo.udf_tokenize大概率使用了XML相关操作（比如XML替换、节点提取），触发了XML解析失败。以下是几种直接可行的解决方法：

方案1：通过XML自动过滤非法字符 #

利用SQL Server的XML转换特性，自动移除所有不符合XML规范的字符，包括那些显示为�的无效字符，同时可以顺便处理换行/回车避免CSV换行：

方式1：创建专用过滤函数 #

CREATE FUNCTION dbo.udf_CleanInvalidChars (@input NVARCHAR(MAX))
RETURNS NVARCHAR(MAX)
AS
BEGIN
    -- 先通过XML过滤非法字符，再替换换行/回车为空格
    RETURN REPLACE(
               REPLACE(
                   CAST(CAST(@input AS XML).value('.', 'NVARCHAR(MAX)') AS NVARCHAR(MAX)),
                   CHAR(10), ' '
               ),
               CHAR(13), ' '
           )
END

方式2：直接在查询中使用（无需创建函数） #

SELECT 
    -- 过滤非法字符+替换换行回车
    sNotes = REPLACE(REPLACE(CAST(CAST(sNotes AS XML).value('.', 'NVARCHAR(MAX)') AS NVARCHAR(MAX)), CHAR(10), ' '), CHAR(13), ' ')
FROM YourTargetTable

方案2：批量过滤控制字符 #

如果明确是不可打印的控制字符（比如ASCII 0-31范围内的非换行/回车字符），可以直接批量替换：

创建控制字符过滤函数 #

CREATE FUNCTION dbo.udf_RemoveControlChars (@input NVARCHAR(MAX))
RETURNS NVARCHAR(MAX)
AS
BEGIN
    DECLARE @i INT = 0
    -- 替换所有ASCII 0-31的控制字符
    WHILE @i <= 31
    BEGIN
        -- 保留换行和回车（如果后续需要处理成空格，可在循环后替换）
        IF @i NOT IN (10,13)
            SET @input = REPLACE(@input, CHAR(@i), '')
        SET @i = @i + 1
    END
    -- 把换行/回车替换为空格，避免CSV生成额外行
    SET @input = REPLACE(REPLACE(@input, CHAR(10), ' '), CHAR(13), ' ')
    RETURN @input
END

调用示例 #

SELECT dbo.udf_RemoveControlChars(sNotes) AS CleanedNotes FROM YourTargetTable

方案3：修复原自定义函数 #

如果不想额外创建函数，直接修改dbo.udf_tokenize，在原有逻辑前加入非法字符过滤步骤：

ALTER FUNCTION dbo.udf_tokenize (@input NVARCHAR(MAX))
RETURNS NVARCHAR(MAX)
AS
BEGIN
    -- 新增：先过滤XML非法字符
    SET @input = CAST(CAST(@input AS XML).value('.', 'NVARCHAR(MAX)') AS NVARCHAR(MAX))
    
    -- 原有函数逻辑...
    -- ...
    
    RETURN @input
END

额外优化：符合CSV规范的字段处理 #

除了移除非法字符，建议用双引号包裹字段值，即使字段中有空格/特殊字符，CSV解析器也不会误判为新行：

SELECT 
    -- 过滤后用双引号包裹，同时转义字段内的双引号（替换为两个双引号）
    sNotes = '"' + REPLACE(dbo.udf_CleanInvalidChars(sNotes), '"', '""') + '"'
FROM YourTargetTable

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