Posted by Erland Sommarskog on 12/06/05 23:55

coosa (coosa76@gmail.com) writes:
> and I'd like to create out of this hierarchy the following desired XML
> file:
>
><?xml version="1.0" encoding="utf-8" ?>
><Hardware>
> <Catgeory name="PC" id="1">
> <Catgeory name="Networks" id="2" />
> <Catgeory name="Audio" id="3" />
> <Catgeory name="Video" id="4">
> <Catgeory name="TV Cards" id="5" />
> <Catgeory name="Graphics Cards" id="6">
> <Catgeory name="AGP" id="7" />
> <Catgeory name="PCI" id="8" />
> </Category>
> </Category>
> <Catgeory name="Input Devices" id="9" />
> </Catgeory>
></Hardware>
>
> The reason for this file is that it will be a datasource of the
> TreeView Control new in asp.net 2.0.
>
> Now, programmateiclally using C#.net i started using the XmlDocument,
> XmlTextWriter and XmlTextReader Namespaces and started using susing
> recurrsion to genearete this desired XML file out of the records in the
> snapshot, but ...
>
> Is there an easy way of doing this using SqlServer 2005 with the new
> datatype XML?
> *Any hint would also be ok*

I'm not so good at XML, so I asked SQL Server MVP Kent Tegels for
help. He gave this example that runs in the AdventureWorks database:

drop function dbo.directReports
go
create function dbo.directReports
(@managerID int,@depth tinyint)
returns xml
as
begin
declare @x xml
if(@managerID is null)
begin
select @x = (
select e.EmployeeID as '@employeeID'
,c.lastName + ', ' + c.firstName as 'name'
,@depth as 'depth'
,dbo.directReports(e.employeeID,0)
from HumanResources.Employee e
join person.contact c
on e.contactID = c.contactID
where e.managerID is null
order by lastName,firstName
for xml path('employee'),type)
end
else
begin
select @x = (
select e.EmployeeID as '@employeeID'
,c.lastName + ', ' + c.firstName as 'name'
,@depth + 1 as 'depth'
,dbo.directReports(e.employeeID,@depth + 1)
from HumanResources.Employee e
join person.contact c
on e.contactID = c.contactID
where e.managerID = @managerID
order by lastName,firstName
for xml path('employee'),type)
end
return @x
end
go

select dbo.directReports(null,0)
from humanresources.employee
where managerID is null
for xml path(''),root('employees'),type
go

I hope you are able to work from this.



--
Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se

Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/prodtechnol/sql/2005/downloads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodinfo/previousversions/books.mspx

[Back to original message]