How to Convert Table Data into xml format using sql with multiple sub nodes

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>
    <Department 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>
    </Department>
    <Department DeptName="DEF"> 
        <Employee EmpID="3">
            <EmpName>David</EmpName>
            <BirthDate>11/09/1982</BirthDate>
        </Employee> 
    </Department>`enter code here
</Employees>

Abdul Rasheed · Accepted Answer · 2016-04-21 11:38:24Z

3

Try this

SELECT  [DeptName] 
        ,(  SELECT  [EmpID],
                    [EmpName],
                    [BirthDate] 
            FROM @table E
            WHERE   E.DeptName = D.DeptName
            FOR XML PATH ('Employee'),TYPE
        )
FROM    @table D
GROUP BY [DEPTNAME]
FOR XML PATH ('Department'),type,ROOT('Employees')

edited Apr 21, 2016 at 11:38

answered Apr 21, 2016 at 11:27

Abdul Rasheed

6,7794 gold badges36 silver badges50 bronze badges

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

1 Comment

Gottfried Lesigang Over a year ago

@saiv One tiny hint: If you want to have the DeptName and the EmpID as attributes (like in the given example), you must add an Alias AS [@DeptName] resp. AS [@EmpID]. And be aware, that XML will use the ISO8601 and therefore the date will look like 1982-09-11T00:00:00

Collectives™ on Stack Overflow

How to Convert Table Data into xml format using sql with multiple sub nodes

1 Answer 1

1 Comment

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Linked

Related