如何将SQL查询结果按分组转换为指定结构的XML？

要实现按id、year、Name分组生成单个<entry>节点，同时把每组的prov和provid转换成带provID属性的<prov>子节点，你可以用嵌套FOR XML PATH+分组的方式来改写SQL，具体代码如下：

SELECT 
    id,
    year,
    Name,
    -- 子查询生成当前分组下的所有<prov>子节点
    (SELECT 
         provid AS '@provID', -- 将provid设为prov节点的属性
         prov AS '.'          -- 将prov设为prov节点的文本内容
     FROM YourTable t2
     WHERE t2.id = t1.id 
       AND t2.year = t1.year 
       AND t2.Name = t1.Name
     FOR XML PATH('prov'), TYPE) -- TYPE确保子XML能正确嵌入主节点
FROM YourTable t1
GROUP BY id, year, Name -- 按指定字段分组，每个组生成一个entry
FOR XML PATH('entry'), ROOT('legref'), ELEMENTS;

关键细节解释： #

• 分组逻辑：外层的GROUP BY id, year, Name保证了每一组唯一的id/year/Name组合只会生成一个<entry>节点，不会像原来那样每行一个。
• 子查询生成子节点：内层查询针对每个分组，把该组下的每一条provid和prov转换成<prov>节点——用@前缀指定属性，.表示节点的文本内容。TYPE关键字很重要，它让子查询返回的是XML类型数据，这样才能正确嵌套到主<entry>节点里，而不是被转成字符串。
• ELEMENTS选项：和你原来的查询保持一致，确保字段都生成对应的XML元素。如果担心分组中存在NULL值导致元素缺失，可以改成ELEMENTS XSINIL，这样会生成带xsi:nil="true"的元素。

最终生成的XML #

执行这个查询后，就能得到你期望的结构：

<legref>
  <entry>
    <id>1</id>
    <year>1995</year>
    <Name>MAC</Name>
    <prov provID="1995-11_CL236">reg 236</prov>
    <prov provID="1995-11_CL230">reg 230 (1)</prov>
    <prov provID="1995-11_CL229J">reg 229J</prov>
    <prov provID="1995-11_CL260">reg 260</prov>
  </entry>
</legref>

原查询的问题所在 #

你原来的查询没有做分组，所以每一行数据都会单独生成一个<entry>节点。通过嵌套FOR XML PATH和分组，我们把同一组的多行数据聚合到了一个<entry>下面，完美匹配需求。

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