SQL Server 2005 - TSQL For Xml query to get grouped departments and employees

Question

For a database holding 3 tables:

1. Company
2. Department
3. EmployeeInDepartment

I would like to a xml like below:

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

Where the company is repeating (tags), departments inside company are repeating (tags) and Employee inside departments are repeating (tags) by repeating I mean there are more than one number of these element & not the data.

Relations

1. Company and Department are related through FK in Department table that links to CompanyId in Company table.

2. Department and EmployeeInDepartment are related through 'FK' in EmployeeInDepartment table that links to DepartmentId in Department table.

Query:

execute the below on PUBS database and check the xml, it will have more than one Jobs with 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

You need to do "nested" FOR XML statements - that should give you what you're looking for.

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

See e.g. Richard Dingwall's Nested FOR XML results with SQL Server that shows how to do this. Of course, you can easily nest more than two levels...

Answer 2

Not entirely sure how you want this but this should give you a start.

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

Result:

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

You should not join all your tables in every part. The main query only queries the information that is needed for the root level and the sub-queries only query the information needed there with a where clause where jobs.job_id = employee.emp_id that filters the rows in the sub queries so you only get the rows you want in the child nodes.