SQL Server XML output with dynamic SQL

Question

I'm faced with a little question on xml output.

I need to get xml structure like this:

and here is my procedure, it'll list all the employees and some of their personal info based on input.

CREATE PROC getDeptEmployees(@deptList varchar(500))
AS
BEGIN
SET NOCOUNT ON 

DECLARE @Query varchar(600)

SET @Query = '
SELECT department.DEPARTMENT_ID [f_Department_ID], department.DEPARTMENT_NAME  [f_Department_name],
dp.Employee_id [f_Employee_ID], dp.First_name [f_First_Name], 
dp.Last_Name [f_Last_name], dp.Email [f_email], dp.Salary [f_salary]
FROM dbo.Employees dp 
JOIN DEPARTMENTS department 
ON dp.DEPARTMENT_ID = department.DEPARTMENT_ID
WHERE dp.Department_id IN (' + @deptList + ') 
for XML AUTO, ROOT(''table'')'

EXEC(@Query)    
END
GO

What I get is here but I still can't figure out how to add the DPR node.

Thank you

Answer 1

You need to use the FOR XML PATH functionality in SQL Server 2005, and a correlated subquery - something along the lines of this:

SELECT 
    d.Department_ID AS '@f_Department_ID',
    d.Department_Name AS '@f_Department_name',
    (SELECT 
        e.BusinessEntityID AS '@f_Employee_ID',
        e.FirstName AS '@f_First_Name',
        e.LastName AS '@f_Last_name',
            e.Email AS '@f_email',
        e.Salary AS '@f_salary'
     FROM
        dbo.Employee e
     WHERE
        e.Department_ID = d.Department_ID
     FOR XML PATH('dp'), TYPE
    ) AS 'dpr' 
FROM 
    dbo.Department d 
WHERE 
    d.DepartmentID IN (.......) 
ORDER BY 
    d.DepartmentID
FOR XML PATH('department'), ROOT('table')

Basically, the innermost query creates the XML element for a single employee

<dp f_Employee_ID="6" f_First_Name="Jossef" f_Last_name="Goldberg" f_salary="998320692" />

Using this as a correlated subquery with an alias of AS 'dpr' then wraps the list of employees for a given department into a <dpr> tag:

<dpr>
  <dp f_Employee_ID="4" f_First_Name="Rob" f_Last_name="Walters" f_salary="112457891" />
  <dp f_Employee_ID="11" f_First_Name="Ovidiu" f_Last_name="Cracium" f_salary="974026903" />
  <dp f_Employee_ID="12" f_First_Name="Thierry" f_Last_name="D'Hers" f_salary="480168528" />
  <dp f_Employee_ID="13" f_First_Name="Janice" f_Last_name="Galvin" f_salary="486228782" />
</dpr>

You then add this XML fragment to the outer most SELECT which creates the <department> node, and you should get the desired output.

For more info on FOR XML PATH see:

• MSDN documentation in the SQL Server Books Online
• FOR XML PATH - A New Mode in FOR XML with SQL Server 2005
• SQL SERVER – Simple Example of Creating XML File Using T-SQL