如何用SQL关联Department与Employee表生成指定结构XML？

需要关联Department表和Employee表，生成符合指定结构的XML文件。要求XML包含所有部门ID，每个<Dept>标签下除DeptID、DeptName外，需将该部门所有员工以独立<EMP>标签（含员工ID）列出。

期望的XML结构 #

<root>
<Dept>
  <DeptID>10</DeptID>
  <DeptName>Research</DeptName>
  <EMP><ID>100</ID></EMP>
  <EMP><ID>200</ID></EMP>
</Dept>

<Dept>
  <DeptID>20</DeptID>
  <DeptName>Finance</DeptName>
  <EMP><ID>300</ID></EMP>
  <EMP><ID>400</ID></EMP>
  <EMP><ID>500</ID></EMP>
  <EMP><ID>600</ID></EMP>
</Dept>
</root>

当前编写的SQL代码片段 #

SELECT 
    D.DeptID,
    D.DeptName,
    -- ** How would I get the EMP details here? **
FROM Department D, Employee E
WHERE Department.DeptID = Employee.DeptID
FOR XML PATH('Dept'), ROOT('root')

完善后的SQL代码及说明 #

原写法会因部门与员工的关联产生重复的<Dept>节点（每个员工对应一个部门节点），不符合需求。需通过嵌套FOR XML的方式，在每个部门下聚合所有员工的<EMP>节点，同时可按需保留无员工的部门。

高效简洁版（无需JOIN和GROUP BY） #

SELECT 
    D.DeptID,
    D.DeptName,
    (
        SELECT E.ID
        FROM Employee E
        WHERE E.DeptID = D.DeptID
        FOR XML PATH('EMP'), TYPE
    )
FROM Department D
FOR XML PATH('Dept'), ROOT('root')

关键说明： #

1. 子查询部分：通过关联当前部门的DeptID，查询该部门下所有员工的ID，用FOR XML PATH('EMP'), TYPE生成每个员工的<EMP><ID>xxx</ID></EMP>节点，TYPE关键字确保生成的XML不会被转义为文本。
2. 主查询直接遍历Department表，子查询自动关联对应员工，无需额外JOIN和分组，保证每个部门仅生成一个<Dept>节点。
3. 若只需保留有员工的部门，可在子查询外添加过滤条件（如EXISTS(SELECT 1 FROM Employee E WHERE E.DeptID = D.DeptID)）。

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