FOR XML PATH无法处理0x001E字符的问题求助

Hey there, let's tackle that error you're hitting. The core issue is your DOCTEXT column contains a control character (0x001E, a record separator) that XML doesn't allow. When using FOR XML PATH(''), SQL Server tries to serialize your data into valid XML, and this character breaks that process entirely.

Quick Solutions to Cleanse Your Data #

You need to handle that invalid character before it gets passed to the FOR XML subquery. Here are two straightforward approaches:

1. Remove the Offending Character Directly

Target the 0x001E character specifically with REPLACE to strip it out before concatenation:

SELECT DISTINCT 
    ID, 
    NAME, 
    DOCTEXT2 
FROM DOC 
CROSS APPLY (
    SELECT Stuff(
        (SELECT ' ' + RTRIM(LTRIM(
            -- Strip out the 0x001E record separator
            REPLACE(DOCTEXT, CHAR(0x001E), '')
        )) 
        FROM DOC d 
        WHERE d.ID=DOC.ID AND d.NAME = DOC.NAME 
        FOR XML PATH (''), TYPE
    ).value('.','varchar(max)'),1,1,'') 
) D (DOCTEXT2)

2. Replace It with a Visible Placeholder

If you don't want to lose track of where that separator was, swap it for a readable placeholder like [RECORD_SEP]:

SELECT DISTINCT 
    ID, 
    NAME, 
    DOCTEXT2 
FROM DOC 
CROSS APPLY (
    SELECT Stuff(
        (SELECT ' ' + RTRIM(LTRIM(
            REPLACE(DOCTEXT, CHAR(0x001E), '[RECORD_SEP]')
        )) 
        FROM DOC d 
        WHERE d.ID=DOC.ID AND d.NAME = DOC.NAME 
        FOR XML PATH (''), TYPE
    ).value('.','varchar(max)'),1,1,'') 
) D (DOCTEXT2)

Future-Proof Fix: Block All XML-Invalid Control Characters #

If you want to avoid similar errors from other control characters (like 0x0000, 0x000B, etc.), use TRANSLATE (available in SQL Server 2017+) to strip out all XML-prohibited control characters in one go:

SELECT DISTINCT 
    ID, 
    NAME, 
    DOCTEXT2 
FROM DOC 
CROSS APPLY (
    SELECT Stuff(
        (SELECT ' ' + RTRIM(LTRIM(
            TRANSLATE(DOCTEXT, 
                CHAR(0x0000)+CHAR(0x0001)+CHAR(0x0002)+CHAR(0x0003)+CHAR(0x0004)+
                CHAR(0x0005)+CHAR(0x0006)+CHAR(0x0007)+CHAR(0x0008)+CHAR(0x000B)+
                CHAR(0x000C)+CHAR(0x000E)+CHAR(0x000F)+CHAR(0x0010)+CHAR(0x0011)+
                CHAR(0x0012)+CHAR(0x0013)+CHAR(0x0014)+CHAR(0x0015)+CHAR(0x0016)+
                CHAR(0x0017)+CHAR(0x0018)+CHAR(0x0019)+CHAR(0x001A)+CHAR(0x001B)+
                CHAR(0x001C)+CHAR(0x001D)+CHAR(0x001E)+CHAR(0x001F),
                REPLICATE('', 29) -- Replace each invalid char with empty string
            )
        )) 
        FROM DOC d 
        WHERE d.ID=DOC.ID AND d.NAME = DOC.NAME 
        FOR XML PATH (''), TYPE
    ).value('.','varchar(max)'),1,1,'') 
) D (DOCTEXT2)

This covers all control characters that XML 1.0 disallows, so you won't run into similar surprises down the line.

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