如何在SELECT...FOR XML中生成作为嵌入XML元素的连接记录？

问题现象 #

使用SQL的FOR XML PATH生成IDOC结构的XML时，原本期望同一<MASTER>节点下包含所有关联的嵌套实例，但实际输出中每个嵌套元素的组合都生成了独立的<IDOC>和<MASTER>节点，结构重复冗余。

问题原因 #

直接关联多个子表会产生笛卡尔积，主表的每条数据与每个子表记录的组合都会形成一行新记录。FOR XML PATH基于每行记录生成对应XML结构，最终导致<MASTER>节点被重复生成。

解决方案 #

通过子查询嵌套生成XML片段的方式，分别为每个嵌套集合生成完整的节点组，再将这些片段嵌入主节点，避免笛卡尔积的影响。

修正后的SQL语句如下：

SELECT
    m.ID AS 'ID',
    m.NAME AS 'NAME',
    -- 生成当前MASTER下所有DESCRIPTION节点
    (SELECT
         d.LANG AS 'LANG',
         d.TEXT AS 'TEXT'
     FROM DESCRIPTION d
     WHERE d.MASTER_ID = m.ID
     FOR XML PATH('DESCRIPTION'), TYPE) AS '',
    -- 生成当前MASTER下所有PERFORMANCE节点
    (SELECT
         p.PARAM AS 'PARAM',
         p.UNIT AS 'UNIT',
         p.VALUE AS 'VALUE'
     FROM PERFORMANCE p
     WHERE p.MASTER_ID = m.ID
     FOR XML PATH('PERFORMANCE'), TYPE) AS ''
FROM MASTER m
WHERE m.ID = 158
FOR XML PATH('MASTER'), ROOT('IDOC')

关键说明 #

• 子查询中使用FOR XML PATH('节点名'), TYPE：TYPE关键字确保子查询返回XML类型而非文本，保留完整节点结构；PATH('节点名')指定子查询结果生成的节点名称。
• 将子查询结果的别名设为空字符串（AS ''），可让嵌套节点直接嵌入<MASTER>节点下，避免被额外父节点包裹。

最终输出效果 #

<IDOC>
  <MASTER>
    <ID>158</ID>
    <NAME>Antenna</NAME>
    <DESCRIPTION>
      <LANG>CZ</LANG>
      <TEXT>Antena Yagi 6el</TEXT>
    </DESCRIPTION>
    <DESCRIPTION>
      <LANG>EN</LANG>
      <TEXT>Antenna Yagi 6 elements</TEXT>
    </DESCRIPTION>
    <PERFORMANCE>
      <PARAM>GAIN</PARAM>
      <UNIT>dBi</UNIT>
      <VALUE>8.5</VALUE>
    </PERFORMANCE>
    <PERFORMANCE>
      <PARAM>F/B RATIO</PARAM>
      <UNIT>dB</UNIT>
      <VALUE>15</VALUE>
    </PERFORMANCE>
    <PERFORMANCE>
      <PARAM>LENGTH</PARAM>
      <UNIT>cm</UNIT>
      <VALUE>120</VALUE>
    </PERFORMANCE>
  </MASTER>
</IDOC>

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