CTE XML Output

I have a organizational table with location->Department->group. Location has many departments and department has many groups. I have only one table to reprasent the above

Id, Name, Parent

Parent is the foreign key tieing back to Id.

Is there any way to use CTE and output an xml like the following?

<Location id="1">
<Name>Location name</Name>
<Departments>
<Department id="2"><Name>Department1</Name></Department>
<Department id="3"><Name>Department2</Name></Department>
</Departments>
</Location>

Thanks,
-keasv

With a fixed hierarchy depth you should be able to use nested selects

select t1.id as "@.id",
t1.Name,
(select t2.id as "@.id",
t2.Name,
(select t3.id as "@.id",
t3.Name
from mytable t3
where t3.parent=t2.id
for xml path('Group'),root('Groups'),type)
from mytable t2
where t2.parent=t1.id
for xml path('Department'),root('Departments'),type)
from mytable t1
where t1.parent is null
for xml path('Location'),type

|||

If the nesting level is not known apriori, you can write a recursive SQL UDF. See the FOR XML Whitepaper at http://msdn.microsoft.com/XML/BuildingXML/XMLandDatabase/default.aspx?pull=/library/en-us/dnsql90/html/forxml2k5.asp.

Best regards

Michael

|||

SQL Server 2005 has a maximum limit of 32 recursively nested function invocations. If your parts hierarchy exceeds the limit, you will need to use the old approach of getting the XML in flat form and applying an XSLT style sheet to create the hierarchy.

Its mentioned here at http://msdn2.microsoft.com/en-us/library/ms345137.aspx

So can anyone suggest any alternate method here?