How to Add Attribute to root node in xml using sql

Question

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>

Plz add one more DeptName in the sample XML to get the complete structure. (eg DeptName="DEF" and employee ) — Abdul Rasheed
– Abdul Rasheed, Commented Apr 27, 2016 at 6:48
i just want to add attribute for root node and that attribute value may be static or any dummy text..(not only dept name) — sai v
– sai v, Commented Apr 27, 2016 at 6:58

Gottfried Lesigang · Accepted Answer · 2016-04-27 07:29:49Z

3

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>

edited Apr 27, 2016 at 7:29

answered Apr 27, 2016 at 7:23

Gottfried Lesigang

67.6k9 gold badges60 silver badges124 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

sai v Over a year ago

Thanks Shnugo for the response.. But i don't need departments tag so the below one is fine for me 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')

Collectives™ on Stack Overflow

How to Add Attribute to root node in xml using sql

1 Answer 1

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related