SQL Server表转XML：为每列添加指定标签与硬编码值的实现方法

问题背景 #

现有SQL查询及生成的XML结构如下：
查询语句：

SELECT 
EmployeeName, RequestStatus
FROM K2.SmartBoxData.Akin_LeaveRequest_Header_SMO
WHERE ID =32
FOR XML PATH ('Message')

生成的XML结果：

<Message>
    <EmployeeName>Developer</EmployeeName>
    <RequestStatus>Line Manager Approval</RequestStatus>
</Message>

需要将每个列单独包装在<tag>节点内，并为每个<tag>添加硬编码的<hardcode>子节点，目标XML结构：

<Message>
  <tag>
     <hardcode> my value </hardcode>
     <EmployeeName>Developer</EmployeeName>
  </tag> 

  <tag>
     <hardcode> my value 2 </hardcode>
     <RequestStatus>Line Manager Approval</RequestStatus>
  </tag>
</Message>

解决方案SQL语句 #

通过UNION ALL拆分列数据为独立行，结合FOR XML PATH的层级命名特性构建嵌套结构：

SELECT
    'my value' AS [tag/hardcode],
    EmployeeName AS [tag/EmployeeName],
    NULL AS [tag/RequestStatus]
FROM K2.SmartBoxData.Akin_LeaveRequest_Header_SMO
WHERE ID = 32

UNION ALL

SELECT
    'my value 2' AS [tag/hardcode],
    NULL AS [tag/EmployeeName],
    RequestStatus AS [tag/RequestStatus]
FROM K2.SmartBoxData.Akin_LeaveRequest_Header_SMO
WHERE ID = 32

FOR XML PATH(''), ROOT('Message')

关键说明 #

1. UNION ALL将两个列的查询结果合并为两行，每行对应一个<tag>节点
2. 使用[tag/hardcode]这种层级命名方式，让SQL自动生成嵌套的XML节点结构
3. 未使用的列设为NULL，避免生成多余的空节点
4. FOR XML PATH('')拼接每行结果为<tag>节点，ROOT('Message')添加指定的根节点

内容的提问来源于stack exchange，提问作者Abidemi McAkin Oluka