xml - SQL Server 2005 - TSQL For Xml 查询以获取分组的部门和员工

Question

对于包含 3 个表的数据库:

1. 公司
2. 部门
3. 部门员工

我想要一个像下面这样的 xml:

<Companies>
  <Company>
    <CompanyName></CompanyName>
    <CompanyId></CompanyId>
    <..></..>
    <Departments>
       <Name></Name>
       <..></..>
       <Employees>
          <Employee>
             <FirstName></FirstName>
             <LastName></LastName>
             .. .. ..
          </Employee>
          .. .. ..
       </Employees>
    </Departments>
    .. .. ..
  </Company>
  .. .. .. 
</Companies>

在公司重复(标签)的地方，公司内部的部门重复(标签)，部门内部的员工重复(标签)，重复我的意思是这些元素有多个而不是数据。

关系

1. Company 和Department 通过Department 中的FK 关联链接到 Company 表中的 CompanyId 的表。

2. Department 和 EmployeeInDepartment 通过链接到 DepartmentId 的 EmployeeInDepartment 表中的“FK”关联在 Department 表中。

查询:

在 PUBS 数据库上执行以下命令并检查 xml，它将有多个 id 为 10 的作业

select jobs.job_id 'JobId',
job_desc 'Desc',
(
    select emp_id 'EmployeeId',fname 'FirstName',lname 'LastName' from employee where job_id = jobs.job_id for xml path('Emploees'),type
)
from jobs
inner join 
employee on  jobs.job_id = employee.job_id
for xml path('employees')

Answer 1

您需要执行“嵌套”FOR XML 语句 - 这应该会为您提供所需的内容。

SELECT
   (some 'Company' columns),
   (SELECT
       (some 'Department' columns),
       (SELECT
           (some 'Employee' columns),
        FROM dbo.EmployeeInDepartment e
        WHERE e.DepartmentId = d.DepartmentId
        FOR XML PATH('Employee'), TYPE
       ) AS 'Employees'
    FROM dbo.Department d
    WHERE d.CompanyId = c.CompanyId
    FOR XML PATH('Department'), TYPE
   ) AS 'Departments'
FROM dbo.Company c
FOR XML PATH('Company'), ROOT('Companies')

参见例如理查德·丁沃尔的 Nested FOR XML results with SQL Server这说明了如何做到这一点。当然，您可以轻松嵌套两层以上...