Geek asks and answers

SQL Server XML output with dynamic SQL

I came across a small question about xml output.

I need to create an xml structure as follows:

and here is my procedure, he will list all the employees and some of their personal data based on the 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

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

thank

+3
source share
1 answer

You need to use the functionality FOR XML PATHin SQL Server 2005, and the correlated subquery is something like 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 an XML element for one 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 AS 'dpr', it then transfers the list of employees for this department to the tag <dpr>:

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

XML SELECT, <department> node, .

FOR XML PATH . :

+3

More articles:


All Articles