Posted by coosa on 12/11/05 16:34

Ok, Erland; it seems I will bother you more! :-) Sorry
First, consider the following query:

--Begin
declare @x as xml
set @x =
(
select CATEGORY_ID as '@ID'
,CATEGORY_NAME as '@NAME'
from CATEGORY
where PARENT_CATEGORY_ID is null
order by DEPTH, CATEGORY_NAME, CATEGORY_ID
for xml path('Category'),root('Categories'),type
)
select @x as [XmlNodes]
--End

The output will be:
<Categories>
<Category ID="3" NAME="CAMERAS" />
<Category ID="2" NAME="MOBILES" />
<Category ID="110" NAME="NOTEBOOK" />
<Category ID="1" NAME="PC HARDWARE" />
</Categories>

This result is an ideal start ...

Now, Consider a partial version of the function to display only the
root categories:
--Begin of function
CREATE function [dbo].[directReports]
(@ParentCatID int)
returns xml
as
begin
declare @x xml
select @x =
(
select CATEGORY_ID as '@ID'
,CATEGORY_NAME as '@NAME'
from CATEGORY
where PARENT_CATEGORY_ID is null
order by DEPTH, CATEGORY_NAME, CATEGORY_ID
for xml path('Category'),type
)
return @x
end
GO
--END of Function

select dbo.directReports(null)
from category
where parent_category_id is null
for xml path(''),root('Categories'),type
go

The output will look like:

<Categories>
<Category ID="3" NAME="CAMERAS" />
<Category ID="2" NAME="MOBILES" />
<Category ID="110" NAME="NOTEBOOK" />
<Category ID="1" NAME="PC HARDWARE" />
<Category ID="3" NAME="CAMERAS" />
<Category ID="2" NAME="MOBILES" />
<Category ID="110" NAME="NOTEBOOK" />
<Category ID="1" NAME="PC HARDWARE" />
<Category ID="3" NAME="CAMERAS" />
<Category ID="2" NAME="MOBILES" />
<Category ID="110" NAME="NOTEBOOK" />
<Category ID="1" NAME="PC HARDWARE" />
<Category ID="3" NAME="CAMERAS" />
<Category ID="2" NAME="MOBILES" />
<Category ID="110" NAME="NOTEBOOK" />
<Category ID="1" NAME="PC HARDWARE" />
</Categories>

Each Category is displayed 4 times, and i tried to use the keyword
DISTINCT but it seems XML doesn't support it.

How can I overcome this problem first?

Best regards

[Back to original message]