CODEWITHSUNDEEP
sqlsql-serverxmlt-sqlattributes
sai v
sai v

Reputation: 183

How to Add Attribute to root node in xml using sql

I have a table structure like below :

SELECT  
    [EmpID], [EmpName],
    [DeptName],
    [BirthDate] 
FROM
    [dbo].[Employees]

I want to convert this table data into XML and the final output will be like below:

<Employees DeptName="ABC">    
  <Employee EmpID="1">
    <EmpName>Davolio</EmpName>
    <BirthDate>10/12/1989</BirthDate>
  </Employee>
  <Employee EmpID="2">
    <EmpName>Andrew</EmpName>    
    <BirthDate>05/02/1985</BirthDate>
  </Employee>    
  <Employee EmpID="3">
    <EmpName>David</EmpName>
    <BirthDate>11/09/1982</BirthDate>
  </Employee>
</Employees>

Upvotes: 4

Views: 717

Answers (1)

Gottfried Lesigang
Gottfried Lesigang

Reputation: 67331

This should get you what you need:

CREATE TABLE dbo.Employees(EmpID INT,EmpName VARCHAR(100),DeptName VARCHAR(100),BirthDate DATE);
INSERT INTO dbo.Employees VALUES
 (1,'Test1','Dep1',{d'1991-01-01'})
,(2,'Test2','Dep1',{d'1992-02-02'})
,(3,'Test3','Dep2',{d'1993-03-03'});
GO

WITH DistinctDepartments AS
(
    SELECT DISTINCT DeptName FROM dbo.Employees
)
SELECT DeptName AS [@DeptName]
      ,(
        SELECT  
         innerEmp.[EmpID] AS [@EmpId]
        ,innerEmp.[EmpName]
        ,innerEmp.[BirthDate] 
        FROM [dbo].Employees AS innerEmp
        WHERE innerEmp.DeptName=DistinctDepartments.DeptName
        FOR XML PATH('Employee'),TYPE
       )
FROM DistinctDepartments
FOR XML PATH('Employees'),ROOT('Departments');

The result

<Departments>
  <Employees DeptName="Dep1">
    <Employee EmpId="1">
      <EmpName>Test1</EmpName>
      <BirthDate>1991-01-01</BirthDate>
    </Employee>
    <Employee EmpId="2">
      <EmpName>Test2</EmpName>
      <BirthDate>1992-02-02</BirthDate>
    </Employee>
  </Employees>
  <Employees DeptName="Dep2">
    <Employee EmpId="3">
      <EmpName>Test3</EmpName>
      <BirthDate>1993-03-03</BirthDate>
    </Employee>
  </Employees>
</Departments>

Upvotes: 3

Related Questions