Adjacency List From SQL Table To XML File

I'm having trouble generating an XML file from a sql table. Basically its an HR Dept. table that has departmentID and ParentDepartmentID. I need to create a hierarchal xml file that has a parent node, and all its departments, and if any of the sub departments have any below them, stick those departments in accordingly. I can do this two ways...either through tSQL or through .NET recursive methods. I've tried the recursion but can't wrap my head around it enough to get it working.

Here is the table schema:




Code BlockCREATE TABLE [dbo].[DepartmentTree](
[DepartmentID] [varchar](13) NOT NULL,
[ParentDepartmentID] [varchar](13) NOT NULL,
[Description] [varchar](30) NOT NULL,
[ManagerEmployeeID] [varchar](13) NOT NULL
) ON [PRIMARY]





here is the xml markup I'm looking for (or facsimile thereof...doesnt really matter)




Code Block
<?xml version="1.0" encoding="UTF-8" ?>
<Department ID="1" Name="IT" Manager="12345">

<Department ID="2" Name="IT Info. Sys" Manager="23456" />
<Department ID="3" Name="IT Technical" Manager="34567">

<Department ID="4" Name="IT Technical West" Manager="45678" />
<Department ID="5" Name="IT Technical East" Manager="56789" />
</Department>
</Department>




Here is my test data:





DepartmentID
ParentDepartmentID
Description
ManagerEmployeeID

1
1
IT
12345

2
1
IT Info. Sys
23456

3
1
IT Technical
34567

4
3
IT Technical West
45678

5
3
IT Technical East
56789



Does anyone have a suggestion as to how to do this?