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

Question

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

1. Company
2. Department
3. EmployeeInDepartment

我想要一个如下所示的 xml：

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

公司重复（标签），公司内部部门重复（标签），部门内部员工重复（标签），我的意思是那里这些元素的数量不只一个。不是数据。

关系

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

2. Department 和 EmployeeInDepartment 通过 EmployeeInDepartment 表中的“FK”关联，该表链接到 DepartmentId 中>部门表。

查询：

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

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')

请参阅 Richard Dingwall 的 使用 SQL Server 嵌套 FOR XML 结果，展示了如何执行此操作。当然，您可以轻松嵌套两层以上......

Answer 2

不完全确定你想要这个，但这应该给你一个开始。

declare @jobs table
(
  job_id int,
  job_desc varchar(10)
)

declare @employee table
(
  emp_id int,
  fname varchar(10),
  lname varchar(10),
  job_id int
)

insert into @jobs values
(1, 'Job 1'),
(2, 'Job 2')

insert into @employee values
(1, 'first 1', 'last 1', 1),
(2, 'first 2', 'last 2', 1),
(3, 'first 3', 'last 3', 2)

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

结果：

<employees>
  <EmployeeId>1</EmployeeId>
  <FirstName>first 1</FirstName>
  <LastName>last 1</LastName>
  <jobs>
    <JobId>1</JobId>
    <Desc>Job 1</Desc>
  </jobs>
</employees>
<employees>
  <EmployeeId>2</EmployeeId>
  <FirstName>first 2</FirstName>
  <LastName>last 2</LastName>
  <jobs>
    <JobId>2</JobId>
    <Desc>Job 2</Desc>
  </jobs>
</employees>
<employees>
  <EmployeeId>3</EmployeeId>
  <FirstName>first 3</FirstName>
  <LastName>last 3</LastName>
</employees>

您不应该在每个部分连接所有表。主查询仅查询根级别所需的信息，子查询仅使用 where 子句 where jobs.job_id = emp_id 来查询根级别所需的信息，该子句会过滤 root 级别中的行。子查询，以便您只能在子节点中获取所需的行。