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>

Answer 1

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