Hierarchy Data in XML

Hello All
I have two Table Employee and Dept
Employee Columns
EmpID
EmpName
MgrID
DeptID
EmpID(PK) MgrID (FK)
Dept COlumns
DeptID
DeptName
I need to return XMl Hierarchy when I pass DeptID as a parameter
So my final result shoudl look like this
<Dept>
<Emp ID="" Name="" ....>
<Emp ID="" Name="">
</Emp>
</Emp>
<Emp ID="" Name="">
</Emp>
<Emp ID="" Name="">
</Emp>
</Dept>
How do I write this Query
Regards
Hello Sekhar,
Take a look at the example I posted yesterday in microsoft.public.sqlserver.programming
in the post about returning arrays.
Thanks,
Kent Tegels
http://staff.develop.com/ktegels/