Posted by Erland Sommarskog on 12/11/05 21:39

So the reason everything is repeated four times is because there are
four top-level nodes, and Kent assumed that there would be only one.
What happens is that the top level gets processed twice. Once in the
outer query, and once in the in the recursive function.

I collapsed those two steps into one:

create function dbo.recursfun (@category int,@depth tinyint)
returns xml as
begin
declare @x xml
select @x = (select CATEGORY_NAME AS '@name',
CATEGORY_ID AS '@id',
@depth + 1 as '@depth',
dbo.recursfun(CATEGORY_ID, @depth + 1)
from CATEGORY
where PARENT_CATEGORY_ID = @category
for xml path('Category'),type)
return @x
end
go

select CATEGORY_NAME as '@Name',
CATEGORY_ID AS '@id',
0 as '@depth',
dbo.recursfun(CATEGORY_ID, 0)
from CATEGORY
where PARENT_CATEGORY_ID IS NULL
for xml path('Category'),root('HARDWARE'), type
go

The result looks good to me...

--
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]