|
|
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
Navigation:
[Reply to this message]
|