SQL Server FOR XML PATH中CHAR(10)未转义为
的问题咨询

我执行以下SQL语句：

SELECT  *
FROM
(
    VALUES 
    (1001, 'LineA1' + CHAR(10) + 'LineA2')
    , (1002, 'LineB1' + CHAR(13) + CHAR(10) + 'LineB2')
) AS T(MyID, MyCol1)
FOR XML PATH('MyGrp1'), ROOT('MyROOT')

得到的结果为：

<MyROOT><MyGrp1><MyID>1001</MyID><MyCol1>LineA1
LineA2</MyCol1></MyGrp1><MyGrp1><MyID>1002</MyID><MyCol1>LineB1&#x0D;
LineB2</MyCol1></MyGrp1></MyROOT>

我希望CHAR(10)在XML结果中输出为
，但当前它直接保留为换行符。我是否误解了CHAR(10)的处理逻辑？

环境：Microsoft SQL Server 2022 (RTM) - 16.0.1000.6 (X64)
补充说明：需要保留列中的原始数据。后续环节会把XML里的换行符转换为<BR>，且无法阻止该行为；但如果XML中是
则会被原样导入，考虑用REPLACE()修改XML来实现需求。

原因说明 #

SQL Server的FOR XML机制中，默认仅对CHAR(13)（回车符）进行XML转义，将其转换为
；而CHAR(10)（换行符）会被保留为原始的换行字符，不会自动转义，这是产品默认的处理逻辑，并非你误解了用法。

解决方案 #

要实现将CHAR(10)转为
且不修改原始数据，可以通过两种方式处理：

方法1：生成XML前替换字符串 #

在SELECT时对目标列进行替换，将CHAR(10)替换为XML实体
，避免FOR XML转义符号&：

SELECT  
    MyID,
    REPLACE(MyCol1, CHAR(10), (SELECT '
' FOR XML PATH(''))) AS MyCol1
FROM
(
    VALUES 
    (1001, 'LineA1' + CHAR(10) + 'LineA2')
    , (1002, 'LineB1' + CHAR(13) + CHAR(10) + 'LineB2')
) AS T(MyID, MyCol1)
FOR XML PATH('MyGrp1'), ROOT('MyROOT')

方法2：生成XML后替换结果 #

先生成原始XML，再将整个XML结果转为字符串后替换其中的CHAR(10)：

DECLARE @xmlResult XML = (
    SELECT  *
    FROM
    (
        VALUES 
        (1001, 'LineA1' + CHAR(10) + 'LineA2')
        , (1002, 'LineB1' + CHAR(13) + CHAR(10) + 'LineB2')
    ) AS T(MyID, MyCol1)
    FOR XML PATH('MyGrp1'), ROOT('MyROOT')
)

SELECT REPLACE(CAST(@xmlResult AS NVARCHAR(MAX)), CHAR(10), '
') AS ModifiedXML

两种方法都能保留原始列数据，同时让XML中的换行符以
的形式输出，避免后续环节将其转换为<BR>。

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