SQL Server 2019如何将行值转换为对应XML标签名与标签值

在SQL Server 2019环境中存在如下结构的临时表：

DROP TABLE IF EXISTS #Temp
SELECT *
INTO #Temp
FROM (
SELECT 1 Id, 'Name' ParameterName, 'John Smith' ParameterValue UNION
SELECT 1 Id, 'Age' ParameterName, '20' ParameterValue UNION 
SELECT 1 Id, 'Gender' ParameterName, 'Male' ParameterValue UNION
SELECT 2 Id, 'Name' ParameterName, 'Jane Smythe' ParameterValue UNION
SELECT 2 Id, 'Age' ParameterName, '26' ParameterValue UNION 
SELECT 2 Id, 'Org Group' ParameterName, 'Marketing' ParameterValue UNION
SELECT 2 Id, 'Gender' ParameterName, 'Female' ParameterValue) t
ORDER BY 1, 2

需要编写查询返回2行结果，每行包含Id字段和一个XML类型列：

• XML列以ParameterName字段的值作为XML标签名
• XML列以ParameterValue字段的值作为对应标签的文本内容

Id=1对应的期望XML结构示例：

<Age>20</Age>
<Gender>Male</Gender>
<Name>John Smith</Name>

先后测试三种FOR XML写法，输出均不符合预期：

-- 写法1：FOR XML AUTO模式
SELECT  t.Id
    , (SELECT       t1.ParameterName  
                , t1.ParameterValue
        FROM        #Temp t1
        WHERE       t1.Id = t.Id
        FOR XML AUTO, TYPE)
FROM    #Temp t
GROUP BY t.Id

-- 写法2：FOR XML RAW ELEMENTS模式
SELECT  t.Id
    , (SELECT       t1.ParameterName  
                , t1.ParameterValue
        FROM        #Temp t1
        WHERE       t1.Id = t.Id
        FOR XML RAW, ELEMENTS, TYPE)
FROM    #Temp t
GROUP BY t.Id

-- 写法3：FOR XML PATH空路径模式
SELECT  t.Id
    , (SELECT       t1.ParameterName  
                , t1.ParameterValue
        FROM        #Temp t1
        WHERE       t1.Id = t.Id
        FOR XML PATH (''), TYPE)
FROM    #Temp t
GROUP BY t.Id

三种写法失效的核心原因是SQL Server原生FOR XML语法默认使用固定列名/行别名作为XML标签名，无法直接将字段值作为动态标签输出：

• FOR XML AUTO会生成以表别名t1为标签名的节点，ParameterName和ParameterValue会作为节点属性存在
• FOR XML RAW, ELEMENTS会为每行生成<row>外层标签，内部嵌套固定名称的<ParameterName>、<ParameterValue>子标签
• FOR XML PATH('')虽然去掉了外层行标签，但依然保留固定名称的<ParameterName>、<ParameterValue>子标签

注意XML规范要求标签名不能包含空格，因此需要先对带空格的参数名（如示例中的Org Group）做合规替换，再通过动态拼接节点+XML类型转换实现需求，同时对参数值做XML转义避免特殊字符（<、>、&等）导致解析失败，可直接运行的代码如下：

SELECT 
    t.Id,
    (
        SELECT 
            CAST(
                N'<' + REPLACE(t1.ParameterName, N' ', N'_') + N'>' + 
                (SELECT t1.ParameterValue FOR XML PATH(''), TYPE).value('.', 'nvarchar(max)') + 
                N'</' + REPLACE(t1.ParameterName, N' ', N'_') + N'>' 
            AS XML)
        FROM #Temp t1
        WHERE t1.Id = t.Id
        FOR XML PATH(''), TYPE
    ) AS ParamsXml
FROM #Temp t
GROUP BY t.Id

执行返回结果：

• Id=1对应XML：<Age>20</Age><Gender>Male</Gender><Name>John Smith</Name>
• Id=2对应XML：<Age>26</Age><Gender>Female</Gender><Name>Jane Smythe</Name><Org_Group>Marketing</Org_Group>

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