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