[SQL] SQL Server extract XML, with child's in separate tags

I have a secnario like,

I have Department and Emplyee table as in pic, I have to join these 2 tables and generate the XML file with all Dept ID's and under each Dept ID, it should list all its employees in individual tags.

Result should look like this

<root>
<Dept>
<DeptID>10</DeptID>
<DeptName>Research</DeptName>
<EMP><ID>100</ID></EMP>
<EMP><ID>200</ID></EMP>
</Dept>

<Dept>
<DeptID>20</DeptID>
<DeptName>Finance</DeptName>
<EMP><ID>300</ID></EMP>
<EMP><ID>400</ID></EMP>
<EMP><ID>500</ID></EMP>
<EMP><ID>600</ID></EMP>
</Dept>
</root>


How would I achieve this ? Under each dept tag, all its employees should be listed as individual tags.

SELECT
D.DeptID ,
D.DeptName,
**How would I get the EMP details here ? **
FROM Department D, Employee E
WHERE Department.DeptID= Employee.DeptID
for xml path('Dept') , root ('root')


Table Details

Continue reading...