Nested Nodes in XML from a table

Dear all,

I have table called CATEGORY, which is defined as follows:

CREATE TABLE CATEGORY
(
CATEGORY_ID INTEGER IDENTITY(1,1) NOT NULL,
CATEGORY_NAME VARCHAR(40) NOT NULL CONSTRAINT
 UC__CATEGORY__CATEGO
RY_NAME UNIQUE,
PARENT_CATEGORY_ID INTEGER,
CATEGORY_ICON IMAGE,
DEPTH INTEGER,
CONSTRAINT PK__CATEGORY PRIMARY KEY (CATEGORY_ID)
)

Supposly, the following snap shot was taken later:

 ====================
 ====================
 ====================
==
| CATEGORY_ID | CATEGORY_NAME  | PARENT_CATEGORY_ID | DEPTH  |
 ====================
 ====================
 ====================
==
| 1           | PC	       | NULL		   | 1	     |
--------------------------------------------------------------
| 2	      | Networks       | 1		   | 2       |
--------------------------------------------------------------
| 3	      | Audio          | 1		   | 2       |
--------------------------------------------------------------
| 4           | Video	       | 1		   | 2       |
--------------------------------------------------------------
| 5	      | TV Cards       | 4		   | 3	     |
--------------------------------------------------------------
| 6           | Graphics Cards | 4		   | 3	     |
--------------------------------------------------------------
| 7	      | AGP	       | 6		   | 4	     |
--------------------------------------------------------------
| 8	      | PCI	       | 6		   | 4	     |
--------------------------------------------------------------
| 9	      | Input Devices  | 1		   | 2	     |
--------------------------------------------------------------

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*

Best regards

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'),typ
e)
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'),typ
e)
end
return @x
end
go

select dbo. directReports(null,0
)
from humanresources.employee
where managerID is null
for xml  path(''),root('emplo
yees'),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/pr...oads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodin...ions/books.mspx

Thanks alot

Thanks Erland,

I have tried the function you wrote me and it works, but i need it in
the format i meantioned earlier; namely:
<?xml version="1.0" encoding="utf-8" ?>
<Hardware>
<Category name="PC" id="1">
<Category name="Networks" id="2" />
<Category name="Audio" id="3" />
<Category name="Video" id="4">
<Category name="TV Cards" id="5" />
<Category name="Graphics Cards" id="6">
<Category name="AGP" id="7" />
<Category name="PCI" id="8" />
</Category>
</Category>
<Category name="Input Devices" id="9" />
</Category>
</Hardware>

The format given by your function creates XML Elements which i don't
want. As a matter of fact, i'm not good at XML either, so I tried to
use the FOR XML AUTO instead FOR XML Path('....'); that generated no
elements, but the attribute names inside of each XML node where made
automatically based on the allias of each table such as: e. and c.
In my case it's only one table and it's made as two allises though in
reality it's the same table; so still i might write select ...  from
category as [Category] ... join Category as [SubCategory]; that will
genareta a meaningful and selfdescribing XML tags but unfortunatelly
will not be convenient for my asp.net web control whereby i need a
standard name; namely CATEGORY.

Best regards

coosa (coosa76@gmail.com)  writes:
> I have tried the function you wrote me and it works, but i need it in
> the format i meantioned earlier; namely:
><?xml version="1.0" encoding="utf-8" ?>
><Hardware>
>         <Category name="PC" id="1">
>                 <Category name="Networks" id="2" />
>                 <Category name="Audio" id="3" />
>                 <Category name="Video" id="4">
>                         <Category name="TV Cards" id="5" />
>                         <Category name="Graphics Cards" id="6">
>                                 <Category name="AGP" id="7" />
>                                 <Category name="PCI" id="8" />
>                         </Category>
>                 </Category>
>                 <Category name="Input Devices" id="9" />
>         </Category>
></Hardware>
>
> The format given by your function creates XML Elements which i don't
> want. As a matter of fact, i'm not good at XML either, so I tried to
> use the FOR XML AUTO instead FOR XML Path('....'); that generated no
> elements,

It appears to be an easy one, but maybe I'm jumping the gun to quickly.

The original function had:

select e.EmployeeID as '@employeeID'
,c.lastName + ', ' + c.firstName as 'name'
,@depth as 'depth'
,dbo.directReports(e.employeeID,0)

If you just change 'name' to '@name' and 'depth' to '@depth' that
should cut it, I think.


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

Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pr...oads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodin...ions/books.mspx

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_CATEGO
RY_ID is null
order by	DEPTH, CATEGORY_NAME, CATEGORY_ID
for xml  path('Category'),roo
 t('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_CATEGO
RY_ID is null
order by	DEPTH, CATEGORY_NAME, CATEGORY_ID
for xml  path('Category'),typ
e
)
return @x
end
GO
--END of Function

select dbo.directReports(null)
from category
where parent_category_id is null
for xml  path(''),root('Categ
ories'),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

coosa (coosa76@gmail.com)  writes:
> Ok, Erland; it seems I will bother you more! :-) Sorry
> 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?

Could you post:

1) CREATE TABLE statements for the table?
2) INSERT statements with the sample data?

It's a little difficult to say something without anything to play around
with.

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

Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pr...oads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodin...ions/books.mspx

USE MASTER
GO

IF DB_ID('TEST_DB') IS NOT NULL
BEGIN
DROP DATABASE TEST_DB
END
GO

CREATE DATABASE TEST_DB
GO

USE TEST_DB
GO

BEGIN TRAN PROCESS_TABLES
GO


CREATE TABLE CATEGORY (
CATEGORY_ID INTEGER IDENTITY(1,1) NOT NULL,
CATEGORY_NAME VARCHAR(40) NOT NULL CONSTRAINT
 UC__CATEGORY__CATEGO
RY_NAME UNIQUE,
PARENT_CATEGORY_ID INTEGER,
DEPTH INTEGER,
CONSTRAINT PK__CATEGORY PRIMARY KEY (CATEGORY_ID)
)
GO


CREATE UNIQUE INDEX  IDX__CATEGORY__CATEG
ORY_NAME ON CATEGORY
(CATEGORY_NAME ASC)
GO


CREATE UNIQUE INDEX  IDX__CATEGORY__CATEG
ORY_ID ON CATEGORY
(CATEGORY_ID)
GO


CREATE TRIGGER AI_CATEGORY
ON CATEGORY
AFTER INSERT AS
UPDATE C
SET    DEPTH = coalesce(p.DEPTH, 0) + 1
FROM   CATEGORY C
JOIN   inserted I ON C.CATEGORY_ID = I.CATEGORY_ID
LEFT   JOIN CATEGORY P ON I.PARENT_CATEGORY_ID = P.CATEGORY_ID
GO


CREATE TRIGGER AU_CATEGORY
ON CATEGORY
AFTER UPDATE AS
DECLARE @LVL  INT
DECLARE @ROWC INT
DECLARE @AFFECTED TABLE (
CATEGORY_ID INT NOT NULL PRIMARY KEY,
LVL         INT NOT NULL
)
SELECT @LVL = 1
INSERT  @AFFECTED(CATEGORY_I
D, LVL)
SELECT CATEGORY_ID,
@LVL
FROM   INSERTED
SELECT @ROWC = @@ROWCOUNT
WHILE  @ROWC <> 0
BEGIN
UPDATE         C
SET            DEPTH = coalesce(P.DEPTH, 0) + 1
FROM           CATEGORY C
LEFT JOIN      CATEGORY P
ON             C.PARENT_CATEGORY_ID = P.CATEGORY_ID
WHERE EXISTS   (
SELECT *
FROM   @AFFECTED A
WHERE  C.CATEGORY_ID = A.CATEGORY_ID
AND    A.LVL = @LVL
)

SELECT         @LVL = @LVL + 1
INSERT         @AFFECTED (CATEGORY_ID, LVL)
SELECT         C.CATEGORY_ID, @LVL
FROM           CATEGORY C
WHERE EXISTS   (
SELECT *
FROM   @AFFECTED A
WHERE  A.CATEGORY_ID = C.PARENT_CATEGORY_ID
)
AND NOT EXISTS (
SELECT *
FROM   @AFFECTED A
WHERE  A.CATEGORY_ID = C.CATEGORY_ID
)
SELECT @ROWC = @@ROWCOUNT
END
GO


INSERT INTO CATEGORY (CATEGORY_NAME) VALUES ('PC HARDWARE')
GO
INSERT INTO CATEGORY (CATEGORY_NAME) VALUES ('MOBILES')
GO
INSERT INTO CATEGORY (CATEGORY_NAME) VALUES ('CAMERAS')
GO
INSERT INTO CATEGORY  (CATEGORY_NAME,PAREN
T_CATEGORY_ID) VALUES
('NETWORKS', 1)
GO
INSERT INTO CATEGORY  (CATEGORY_NAME,PAREN
T_CATEGORY_ID) VALUES
('AUDIO', 1)
GO
INSERT INTO CATEGORY  (CATEGORY_NAME,PAREN
T_CATEGORY_ID) VALUES
('VIDEO', 1)
GO
INSERT INTO CATEGORY  (CATEGORY_NAME,PAREN
T_CATEGORY_ID) VALUES ('HARD
DRIVES', 1)
GO
INSERT INTO CATEGORY  (CATEGORY_NAME,PAREN
T_CATEGORY_ID) VALUES
('MEMORY', 1)
GO
INSERT INTO CATEGORY  (CATEGORY_NAME,PAREN
T_CATEGORY_ID) VALUES
('MOTHERBOARDS', 1)
GO
INSERT INTO CATEGORY  (CATEGORY_NAME,PAREN
T_CATEGORY_ID) VALUES ('CPU',
1)
GO
INSERT INTO CATEGORY  (CATEGORY_NAME,PAREN
T_CATEGORY_ID) VALUES
('SWITCHES', 4)
GO
INSERT INTO CATEGORY  (CATEGORY_NAME,PAREN
T_CATEGORY_ID) VALUES ('HUBS',
4)
GO
INSERT INTO CATEGORY  (CATEGORY_NAME,PAREN
T_CATEGORY_ID) VALUES
('MODEMS', 4)
GO
INSERT INTO CATEGORY  (CATEGORY_NAME,PAREN
T_CATEGORY_ID) VALUES
('ROUTERS', 4)
GO
INSERT INTO CATEGORY  (CATEGORY_NAME,PAREN
T_CATEGORY_ID) VALUES
('BRIDGES', 4)
GO
INSERT INTO CATEGORY  (CATEGORY_NAME,PAREN
T_CATEGORY_ID) VALUES
('EXTERNAL MODEMS', 13)
GO
INSERT INTO CATEGORY  (CATEGORY_NAME,PAREN
T_CATEGORY_ID) VALUES
('INTERNAL MODEMS', 13)
GO
INSERT INTO CATEGORY  (CATEGORY_NAME,PAREN
T_CATEGORY_ID) VALUES ('DSL
MODEMS', 16)
GO
INSERT INTO CATEGORY  (CATEGORY_NAME,PAREN
T_CATEGORY_ID) VALUES ('ISDN
MODEMS', 16)
GO
INSERT INTO CATEGORY  (CATEGORY_NAME,PAREN
T_CATEGORY_ID) VALUES
('GRAPHIC CARDS', 6)
GO
INSERT INTO CATEGORY  (CATEGORY_NAME,PAREN
T_CATEGORY_ID) VALUES ('AGP
GRAPHIC CARDS', 20)
GO
INSERT INTO CATEGORY  (CATEGORY_NAME,PAREN
T_CATEGORY_ID) VALUES ('PCI
GRAPHIC CARDS', 20)
GO
INSERT INTO CATEGORY  (CATEGORY_NAME,PAREN
T_CATEGORY_ID) VALUES ('AUDIO
CARDS', 5)
GO
INSERT INTO CATEGORY  (CATEGORY_NAME,PAREN
T_CATEGORY_ID) VALUES ('PCI
AUDIO CARDS', 23)
GO
INSERT INTO CATEGORY  (CATEGORY_NAME,PAREN
T_CATEGORY_ID) VALUES ('ISA
AUDIO CARDS', 23)
GO
INSERT INTO CATEGORY  (CATEGORY_NAME,PAREN
T_CATEGORY_ID) VALUES
('SPEAKER SYSTEMS', 5)
GO
INSERT INTO CATEGORY  (CATEGORY_NAME,PAREN
T_CATEGORY_ID) VALUES ('2.0
SPEAKER SYSTEMS', 26)
GO
INSERT INTO CATEGORY  (CATEGORY_NAME,PAREN
T_CATEGORY_ID) VALUES ('2.1
SPEAKER SYSTEMS', 26)
GO
INSERT INTO CATEGORY  (CATEGORY_NAME,PAREN
T_CATEGORY_ID) VALUES ('4.1
SPEAKER SYSTEMS', 26)
GO
INSERT INTO CATEGORY  (CATEGORY_NAME,PAREN
T_CATEGORY_ID) VALUES ('5.1
SPEAKER SYSTEMS', 26)
GO
INSERT INTO CATEGORY  (CATEGORY_NAME,PAREN
T_CATEGORY_ID) VALUES ('6.1
SPEAKER SYSTEMS', 26)
GO
INSERT INTO CATEGORY  (CATEGORY_NAME,PAREN
T_CATEGORY_ID) VALUES ('7.1
SPEAKER SYSTEMS', 26)
GO
INSERT INTO CATEGORY  (CATEGORY_NAME,PAREN
T_CATEGORY_ID) VALUES
('SPEAKER DECODER', 26)
GO
INSERT INTO CATEGORY  (CATEGORY_NAME,PAREN
T_CATEGORY_ID) VALUES ('HEAD
PHONES', 5)
GO
INSERT INTO CATEGORY  (CATEGORY_NAME,PAREN
T_CATEGORY_ID) VALUES ('EAR
PHONES', 5)
GO
INSERT INTO CATEGORY  (CATEGORY_NAME,PAREN
T_CATEGORY_ID) VALUES ('AUDIO
ACCESSORIES', 5)
GO
INSERT INTO CATEGORY  (CATEGORY_NAME,PAREN
T_CATEGORY_ID) VALUES
('EXTERNAL DEVICES', 5)
GO
INSERT INTO CATEGORY  (CATEGORY_NAME,PAREN
T_CATEGORY_ID) VALUES
('DUPLICATION', 7)
GO
INSERT INTO CATEGORY  (CATEGORY_NAME,PAREN
T_CATEGORY_ID) VALUES
('ENCLOSURE', 7)
GO
INSERT INTO CATEGORY  (CATEGORY_NAME,PAREN
T_CATEGORY_ID) VALUES
('INTERNAL SCSI', 7)
GO
INSERT INTO CATEGORY  (CATEGORY_NAME,PAREN
T_CATEGORY_ID) VALUES ('SERIAL
ATA', 7)
GO
INSERT INTO CATEGORY  (CATEGORY_NAME,PAREN
T_CATEGORY_ID) VALUES
('FIREWIRE', 7)
GO
INSERT INTO CATEGORY  (CATEGORY_NAME,PAREN
T_CATEGORY_ID) VALUES
('EXTERNAL USB', 7)
GO
INSERT INTO CATEGORY  (CATEGORY_NAME,PAREN
T_CATEGORY_ID) VALUES
('NETWORK ATTACHED STORAG', 7)
GO
INSERT INTO CATEGORY  (CATEGORY_NAME,PAREN
T_CATEGORY_ID) VALUES
('INTERNAL IDE', 7)
GO
INSERT INTO CATEGORY  (CATEGORY_NAME,PAREN
T_CATEGORY_ID) VALUES ('TV
CARDS', 6)
GO
INSERT INTO CATEGORY  (CATEGORY_NAME,PAREN
T_CATEGORY_ID) VALUES ('AGP
016MB', 21)
GO
INSERT INTO CATEGORY  (CATEGORY_NAME,PAREN
T_CATEGORY_ID) VALUES ('AGP
032MB', 21)
GO
INSERT INTO CATEGORY  (CATEGORY_NAME,PAREN
T_CATEGORY_ID) VALUES ('AGP
064MB', 21)
GO
INSERT INTO CATEGORY  (CATEGORY_NAME,PAREN
T_CATEGORY_ID) VALUES ('AGP
128MB', 21)
GO
INSERT INTO CATEGORY  (CATEGORY_NAME,PAREN
T_CATEGORY_ID) VALUES ('AGP
256MB', 21)
GO
INSERT INTO CATEGORY  (CATEGORY_NAME,PAREN
T_CATEGORY_ID) VALUES ('AGP
512MB', 21)
GO
INSERT INTO CATEGORY  (CATEGORY_NAME,PAREN
T_CATEGORY_ID) VALUES
('CONTROLLER',1)
GO
INSERT INTO CATEGORY  (CATEGORY_NAME,PAREN
T_CATEGORY_ID) VALUES
('SCANNER',1)
GO
INSERT INTO CATEGORY  (CATEGORY_NAME,PAREN
T_CATEGORY_ID) VALUES
('CASING',1)
GO
INSERT INTO CATEGORY  (CATEGORY_NAME,PAREN
T_CATEGORY_ID) VALUES
('OPTICAL DEVICES',1)
GO
INSERT INTO CATEGORY  (CATEGORY_NAME,PAREN
T_CATEGORY_ID) VALUES
('DISPLAY',1)
GO
INSERT INTO CATEGORY  (CATEGORY_NAME,PAREN
T_CATEGORY_ID) VALUES ('INPUT
DEVICES',1)
GO
INSERT INTO CATEGORY  (CATEGORY_NAME,PAREN
T_CATEGORY_ID) VALUES
('COOLER',1)
GO
INSERT INTO CATEGORY  (CATEGORY_NAME,PAREN
T_CATEGORY_ID) VALUES
('POWER',1)
GO
INSERT INTO CATEGORY  (CATEGORY_NAME,PAREN
T_CATEGORY_ID) VALUES
('PRINTER',1)
GO
INSERT INTO CATEGORY  (CATEGORY_NAME,PAREN
T_CATEGORY_ID) VALUES
('EIDE-ATA',53)
GO
INSERT INTO CATEGORY  (CATEGORY_NAME,PAREN
T_CATEGORY_ID) VALUES
('SCSI-RAID',53)
GO
INSERT INTO CATEGORY  (CATEGORY_NAME,PAREN
T_CATEGORY_ID) VALUES
('SCSI',53)
GO
INSERT INTO CATEGORY  (CATEGORY_NAME,PAREN
T_CATEGORY_ID) VALUES ('PCMCIA
INTERFACE',53)
GO
INSERT INTO CATEGORY  (CATEGORY_NAME,PAREN
T_CATEGORY_ID) VALUES ('ISA
INTERFACE',53)
GO
INSERT INTO CATEGORY  (CATEGORY_NAME,PAREN
T_CATEGORY_ID) VALUES ('PCI
INTERFACE',53)
GO
INSERT INTO CATEGORY  (CATEGORY_NAME,PAREN
T_CATEGORY_ID) VALUES
('CD-R',56)
GO
INSERT INTO CATEGORY  (CATEGORY_NAME,PAREN
T_CATEGORY_ID) VALUES
('CD-RW',56)
GO
INSERT INTO CATEGORY  (CATEGORY_NAME,PAREN
T_CATEGORY_ID) VALUES
('DVD-R',56)
GO
INSERT INTO CATEGORY  (CATEGORY_NAME,PAREN
T_CATEGORY_ID) VALUES
('DVD-RW',56)
GO
INSERT INTO CATEGORY  (CATEGORY_NAME,PAREN
T_CATEGORY_ID) VALUES
('MONITOR',57)
GO
INSERT INTO CATEGORY  (CATEGORY_NAME,PAREN
T_CATEGORY_ID) VALUES
('PLASMA',57)
GO
INSERT INTO CATEGORY  (CATEGORY_NAME,PAREN
T_CATEGORY_ID) VALUES
('PROJECTOR',57)
GO
INSERT INTO CATEGORY  (CATEGORY_NAME,PAREN
T_CATEGORY_ID) VALUES ('TOUCH
SCREEN',57)
GO
INSERT INTO CATEGORY  (CATEGORY_NAME,PAREN
T_CATEGORY_ID) VALUES
('DISPLAY ACCESSORIES',57)
GO
INSERT INTO CATEGORY  (CATEGORY_NAME,PAREN
T_CATEGORY_ID) VALUES
('TABLETS & HANDWRITING',58)
GO
INSERT INTO CATEGORY  (CATEGORY_NAME,PAREN
T_CATEGORY_ID) VALUES
('JOYSTICK & GAME PAD',58)
GO
INSERT INTO CATEGORY  (CATEGORY_NAME,PAREN
T_CATEGORY_ID) VALUES
('KEYBOARD & KEYPAD',58)
GO
INSERT INTO CATEGORY  (CATEGORY_NAME,PAREN
T_CATEGORY_ID) VALUES
('MICE',58)
GO
INSERT INTO CATEGORY  (CATEGORY_NAME,PAREN
T_CATEGORY_ID) VALUES ('MOUSE
PAD & WRIST REST',58)
GO
INSERT INTO CATEGORY  (CATEGORY_NAME,PAREN
T_CATEGORY_ID) VALUES
('MICROPHONE',58)
GO
INSERT INTO CATEGORY  (CATEGORY_NAME,PAREN
T_CATEGORY_ID) VALUES
('THERMAL GEL',59)
GO
INSERT INTO CATEGORY  (CATEGORY_NAME,PAREN
T_CATEGORY_ID) VALUES ('LIQUID
COOLING',59)
GO
INSERT INTO CATEGORY  (CATEGORY_NAME,PAREN
T_CATEGORY_ID) VALUES ('FAN
CONTROLLER',59)
GO
INSERT INTO CATEGORY  (CATEGORY_NAME,PAREN
T_CATEGORY_ID) VALUES ('HARD
DRIVE COOLER',59)
GO
INSERT INTO CATEGORY  (CATEGORY_NAME,PAREN
T_CATEGORY_ID) VALUES ('CPU
FAN',59)
GO
INSERT INTO CATEGORY  (CATEGORY_NAME,PAREN
T_CATEGORY_ID) VALUES ('FAN
FUNNEL',59)
GO
INSERT INTO CATEGORY  (CATEGORY_NAME,PAREN
T_CATEGORY_ID) VALUES
('SHIM',59)
GO
INSERT INTO CATEGORY  (CATEGORY_NAME,PAREN
T_CATEGORY_ID) VALUES
('CHIPSET FAN',59)
GO
INSERT INTO CATEGORY  (CATEGORY_NAME,PAREN
T_CATEGORY_ID) VALUES ('CASE
FAN',59)
GO
INSERT INTO CATEGORY  (CATEGORY_NAME,PAREN
T_CATEGORY_ID) VALUES ('MEMORY
COOLER',59)
GO
INSERT INTO CATEGORY  (CATEGORY_NAME,PAREN
T_CATEGORY_ID) VALUES ('POWER
SUPPLY',60)
GO
INSERT INTO CATEGORY  (CATEGORY_NAME,PAREN
T_CATEGORY_ID) VALUES ('POWER
PROTECTION',60)
GO
INSERT INTO CATEGORY  (CATEGORY_NAME,PAREN
T_CATEGORY_ID) VALUES ('POWER
ACCESSORIES',60)
GO
INSERT INTO CATEGORY  (CATEGORY_NAME,PAREN
T_CATEGORY_ID) VALUES ('SINGLE
PRINTER',61)
GO
INSERT INTO CATEGORY  (CATEGORY_NAME,PAREN
T_CATEGORY_ID) VALUES ('MFC -
ALL IN ONE',61)
GO
INSERT INTO CATEGORY  (CATEGORY_NAME,PAREN
T_CATEGORY_ID) VALUES
('PRINTER ACCESSORIES',61)
GO
INSERT INTO CATEGORY  (CATEGORY_NAME,PAREN
T_CATEGORY_ID) VALUES
('NETWORK ACCESSORIES',4)
GO
INSERT INTO CATEGORY  (CATEGORY_NAME,PAREN
T_CATEGORY_ID) VALUES
('VOIP',4)
GO
INSERT INTO CATEGORY  (CATEGORY_NAME,PAREN
T_CATEGORY_ID) VALUES
('KVM',4)
GO
INSERT INTO CATEGORY  (CATEGORY_NAME,PAREN
T_CATEGORY_ID) VALUES
('GATEWAY',4)
GO
INSERT INTO CATEGORY  (CATEGORY_NAME,PAREN
T_CATEGORY_ID) VALUES
('FIREWALL',4)
GO
INSERT INTO CATEGORY  (CATEGORY_NAME,PAREN
T_CATEGORY_ID) VALUES
('NETWORK CABLE',4)
GO
INSERT INTO CATEGORY  (CATEGORY_NAME,PAREN
T_CATEGORY_ID) VALUES
('STARTER KIT',4)
GO
INSERT INTO CATEGORY  (CATEGORY_NAME,PAREN
T_CATEGORY_ID) VALUES
('ATTACHED STORAGE',4)
GO
INSERT INTO CATEGORY  (CATEGORY_NAME,PAREN
T_CATEGORY_ID) VALUES
('ETHERNET ADAPTER',4)
GO
INSERT INTO CATEGORY  (CATEGORY_NAME,PAREN
T_CATEGORY_ID) VALUES ('PATCH
PANEL',4)
GO
INSERT INTO CATEGORY  (CATEGORY_NAME,PAREN
T_CATEGORY_ID) VALUES ('MEDIA
CONVERTER',4)
GO
INSERT INTO CATEGORY (CATEGORY_NAME) VALUES ('NOTEBOOK')
GO

COMMIT TRAN PROCESS_TABLES
GO

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_I
D, @depth + 1)
from  CATEGORY
where PARENT_CATEGORY_ID = @category
for xml  path('Category'),typ
e)
return @x
end
go

select CATEGORY_NAME as '@Name',
CATEGORY_ID   AS '@id',
0 as '@depth',
dbo. recursfun(CATEGORY_I
D, 0)
from  CATEGORY
where PARENT_CATEGORY_ID IS NULL
for xml  path('Category'),roo
t('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/pr...oads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodin...ions/books.mspx

Thanks Erland, it works now.
I'm facing now another Issue :-D
Now in asp.net I have created a streamfile and gave it a .xml
extension, added the missing XML declaration line, SQL Server didn't
provide, and every thing works fine, so far, but the whole purpose of
doing of this was to increase the performance of the server; the
concept was as follows:

I don't want every time a client openes the page to access and process
queries from SQL Server, but i rather create XML file and read from it
to display the categories AS LONG the categories in the DB have not
been changed; if changed then for one time, the XML file gets updated
and so on.
So I'd like to create a trigger that would perform the following:
Execute .exe File located in a known path for SQL server

This .exe is a compiled .net code that would automatically query the
XML results and update the XML file file.

Is it possible to do some thing like this with a trigger?

Thanks in adcance