SQL Server中为FOR XML PATH查询结果添加固定XML前后缀的方法

问题原因 #

你之前拼接出现<、>被转义的问题，是因为SQL Server中FOR XML查询默认返回XML类型，当XML类型和字符串直接拼接时，数据库会自动对XML中的特殊字符做转义处理，避免格式冲突。可以用以下两种方案解决：

方案1：嵌套FOR XML查询（更推荐） #

完全遵循SQL Server的XML生成规则，不会出现转义和格式错误，不需要手动拼接字符串：

SELECT
    '2.0' AS VERSION,
    'Henrov' AS LICENTIEHOUDER,
    'BulkImport' AS XTN,
    'Name' AS ARCHIEFNAAM,
    'Label1' AS ITEMLABEL1,
    'Label2' AS ITEMLABEL2,
    'Label3' AS ITEMLABEL3,
    'Label4' AS ITEMLABEL4,
    'Label5' AS ITEMLABEL5,
    'Label6' AS ITEMLABEL6,
    'Label7' AS ITEMLABEL7,
    'Label8' AS ITEMLABEL8,
    'Label9' AS ITEMLABEL9,
    'Label0' AS ITEMLABEL10,
    'Label1' AS ITEMLABEL11,
    'Label2' AS ITEMLABEL12,
    'Label3' AS ITEMLABEL13,
    'Label4' AS ITEMLABEL14,
    'Label5' AS ITEMLABEL15,
    'Label6' AS ITEMLABEL16,
    'Label7' AS ITEMLABEL17,
    -- 嵌套生成所有DOCUMENT节点，TYPE关键字保持XML类型不转义
    (
        SELECT *
        FROM [staging].[vw_mydata]
        FOR XML PATH('DOCUMENT'), TYPE
    )
-- PATH('')避免外层多套节点，ROOT自动生成DOCUMENTS根节点
FOR XML PATH(''), ROOT('DOCUMENTS'), TYPE

如果需要添加XML声明头，只需要把最终输出结果转成字符串后，在开头拼接<?xml version="1.0"?>即可。

方案2：先转字符串再拼接 #

适合不想改动固定前缀内容的场景，直接把XML结果转成字符串后再做拼接：

DECLARE @docXml XML, @fullResult NVARCHAR(MAX)

-- 生成所有DOCUMENT节点的XML内容
SET @docXml = (
    SELECT *
    FROM [staging].[vw_mydata]
    FOR XML PATH('DOCUMENT'), TYPE
)

-- 拼接固定前缀、转成字符串的XML内容、闭合标签
SET @fullResult = N'<?xml version="1.0"?>
<DOCUMENTS>
    <VERSION>2.0</VERSION>
    <LICENTIEHOUDER>Henrov</LICENTIEHOUDER>
    <XTN>BulkImport</XTN>
    <ARCHIEFNAAM>Name</ARCHIEFNAAM>
    <ITEMLABEL1>Label1</ITEMLABEL1>
    <ITEMLABEL2>Label2</ITEMLABEL2>
    <ITEMLABEL3>Label3</ITEMLABEL3>
    <ITEMLABEL4>Label4</ITEMLABEL4>
    <ITEMLABEL5>Label5</ITEMLABEL5>
    <ITEMLABEL6>Label6</ITEMLABEL6>
    <ITEMLABEL7>Label7</ITEMLABEL7>
    <ITEMLABEL8>Label8</ITEMLABEL8>
    <ITEMLABEL9>Label9</ITEMLABEL9>
    <ITEMLABEL10>Label0</ITEMLABEL10>
    <ITEMLABEL11>Label1</ITEMLABEL11>
    <ITEMLABEL12>Label2</ITEMLABEL12>
    <ITEMLABEL13>Label3</ITEMLABEL13>
    <ITEMLABEL14>Label4</ITEMLABEL14>
    <ITEMLABEL15>Label5</ITEMLABEL15>
    <ITEMLABEL16>Label6</ITEMLABEL16>
    <ITEMLABEL17>Label7</ITEMLABEL17>
' + CAST(@docXml AS NVARCHAR(MAX)) + N'
</DOCUMENTS>'

-- 输出最终结果
SELECT @fullResult

补充说明 #

你提供的原始输出示例中第二个<DOCUMENT>节点缺少开头标签，属于示例笔误，使用上述两种方案生成时，每一行数据都会自动被<DOCUMENT>标签包裹，不会出现该问题。

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