SQL生成XML时如何让attribute标签独立重复生成

原查询中，由于重复声明[subscriber/attribute/attr_name]和[subscriber/attribute/attr_value]列，SQL Server会将多组属性合并到同一个<attribute>节点内。要实现每个属性对应独立的<attribute>标签，需调整查询结构，通过拆分属性为独立行再嵌套生成XML：

修改后的SQL查询语句 #

SELECT
    CAST(415 as int) as 'version',
    CAST(1942605 as int) as 'form_id',
    CAST(1145 as int) as 'customer_id',
    (
        SELECT
            'emailgeneric' AS [attr_name],
            U.Email AS [attr_value]
        FOR XML PATH('attribute'), TYPE
        UNION ALL
        SELECT
            'pnmgiven' AS [attr_name],
            U.Name AS [attr_value]
        FOR XML PATH('attribute'), TYPE
    ) AS [subscriber]
FROM [example.com].[dbo].[KMU_Users] AS U
LEFT JOIN [example.com].[dbo].[Address] AS A ON U.UserID=A.UserID
LEFT JOIN [example.com].[dbo].[Provinces] AS P ON P.Code=A.City
WHERE Email='example@hotmail.com'
FOR XML PATH('subscription_data')

关键调整说明 #

1. 将每个属性拆分为独立的SELECT语句，用UNION ALL合并成多行数据
2. 每个属性查询通过FOR XML PATH('attribute'), TYPE生成独立的<attribute>节点，TYPE参数确保XML片段被正确嵌套
3. 将所有属性的XML片段作为<subscriber>节点的内容，最终组合成目标结构

生成的XML结果 #

<subscription_data>
  <version>415</version>
  <form_id>1942605</form_id>
  <customer_id>1145</customer_id>
  <subscriber>
    <attribute>
      <attr_name>emailgeneric</attr_name>
      <attr_value>example@hotmail.com</attr_value>
    </attribute>
    <attribute>
      <attr_name>pnmgiven</attr_name>
      <attr_value>John</attr_value>
    </attribute>
  </subscriber>
</subscription_data>

内容的提问来源于stack exchange，提问作者Eymen Batın Yacı