如何用T-SQL将重复XML节点合并为单个节点而非重复生成

问题场景 #

使用T-SQL为第三方应用生成XML输出时，Metadata节点会随源数据分组重复出现，需要让该节点仅在XML顶部显示一次，不受数据行数影响。

修改后的查询语句 #

SELECT
    -- 单独提取Metadata节点，确保仅返回一次
    (SELECT TOP 1
         data_timestamp AS 'data_timestamp',
         questiontype AS 'questiontype',
         legalbasis AS 'legalbasis'
     FROM #XML
     FOR XML PATH('Metadata'), TYPE)
    -- 保留原有主体数据逻辑，按party_id生成对应节点
    ,(SELECT
         code AS 'notification/code',
         [description] AS 'notification/description',
         date_of_birth AS 'register/natural_person/date_of_birth',
         initials_np AS 'register/natural_person/name/Initials',
         first_name AS 'register/natural_person/name/first_name',
         last_name AS 'register/natural_person/name/last_name',
         (SELECT
              B.city,
              B.address_type,
              B.address
          FROM #XML AS B
          WHERE B.party_id = T.party_id
          FOR XML PATH('address'), TYPE)
     FROM #XML T
     GROUP BY party_id, data_timestamp, active_customer, code, [description],
              date_of_birth, initials_np, first_name, last_name, questiontype, legalbasis
     FOR XML PATH(''), TYPE)
FOR XML PATH(''), ROOT('bdrp_client_response'), TYPE, ELEMENTS XSINIL;

逻辑说明 #

1. 分离固定节点：通过子查询单独提取Metadata内容，用TOP 1确保仅生成一组节点（若存在多组不同Metadata，可根据实际需求改用DISTINCT或添加筛选条件）。
2. 保留业务节点逻辑：原有的客户分组、地址嵌套查询逻辑保持不变，确保每个party_id对应的notification、register和address节点正常生成。
3. 组合输出结构：将固定节点子查询和业务节点子查询的结果通过外层FOR XML PATH('')拼接，最终套上根节点bdrp_client_response，实现固定节点仅在顶部显示一次的效果。

最终XML输出 #

<bdrp_client_response xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance">
  <Metadata>
    <data_timestamp>2022-10-18T22:44:31.727</data_timestamp>
    <questiontype>Customer</questiontype>
    <legalbasis>legal01</legalbasis>
  </Metadata>
  <notification>
    <code>Customer Found</code>
    <description>Valid</description>
  </notification>
  <register>
    <natural_person>
      <date_of_birth>1979-11-05</date_of_birth>
      <name>
        <Initials>JBS</Initials>
        <first_name>John</first_name>
        <last_name>Smith</last_name>
      </name>
    </natural_person>
  </register>
  <address>
    <city>Amsterdam</city>
    <address_type>Primary Address</address_type>
    <address>5 Cod street</address>
  </address>
  <address>
    <city>Rotterdam</city>
    <address_type>Secondary Address</address_type>
    <address>4 Pike street</address>
  </address>
  <notification>
    <code>Customer not Found</code>
    <description xsi:nil="true" />
  </notification>
  <register>
    <natural_person>
      <date_of_birth xsi:nil="true" />
      <name>
        <Initials xsi:nil="true" />
        <first_name xsi:nil="true" />
        <last_name xsi:nil="true" />
      </name>
    </natural_person>
  </register>
  <address />
</bdrp_client_response>

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