Nested Nodes in XML from a table

[coosa]

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

[Erland Sommarskog]

coosa (co*****@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, es****@sommarskog.se

Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pro...ads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodinf...ons/books.mspx

[coosa]

Thanks alot

[coosa]

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

[Erland Sommarskog]

coosa (co*****@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, es****@sommarskog.se

Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pro...ads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodinf...ons/books.mspx

[coosa]

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

[Erland Sommarskog]

coosa (co*****@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, es****@sommarskog.se

Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pro...ads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodinf...ons/books.mspx

[coosa]

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

COMMIT TRAN PROCESS_TABLES
GO

[Erland Sommarskog]

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, es****@sommarskog.se

Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pro...ads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodinf...ons/books.mspx

[coosa]

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

[Erland Sommarskog]

coosa (co*****@gmail.com) writes:

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?

Possible? Yes, if the SQL Server machine can access the disk where the
file is. But, no, go there. A trigger is part of transaction, and should
leave as little footprint as possible. Running around and updating files
on a client is a poor idea.

Rather, I would suggest that you look into query notification. You can
submit a query, and then specify that you want to be notified when the
query changes. This requires SQL 2005 and ADO .Net 2.0. I'm not going
to show any examples here, but please look in Books Online.

I will have to admit that I not entirely convinced that this is a good
idea. I would probably get the XML from the database each time. But
that's your call.
--
Erland Sommarskog, SQL Server MVP, es****@sommarskog.se

Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pro...ads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodinf...ons/books.mspx

[coosa]

Well considering that many persons would be accessing a search engine;
who knows how many of them will be in the future acessing at the same
time; wouldn't that be pressuring the database engine much?

I have thought about the idea due to the fact that accessing XML filles
is much faster than accessing the actual sql server.
It's still experimental and i'm totally new in this field.
But can you at least give me the term name used for such methodolgy you
mentioned so that i can search for that term and see examples?

Best regards

[Erland Sommarskog]

coosa (co*****@gmail.com) writes:

Well considering that many persons would be accessing a search engine;
who knows how many of them will be in the future acessing at the same
time; wouldn't that be pressuring the database engine much?
Maybe. Then again, an implementation of low complexity is always appealing.
Doing things like caching a file, is also a thing that can be saved until
later, when you have the basic functionality going.
I have thought about the idea due to the fact that accessing XML filles
is much faster than accessing the actual sql server.
It's still experimental and i'm totally new in this field.
But can you at least give me the term name used for such methodolgy you
mentioned so that i can search for that term and see examples?

The term *is* Query Notification, and I encourage you to read about it
in Books Online. I don't have Books Online 2005 on this machine, so I cannot
give you a direct link. But use the index.
--
Erland Sommarskog, SQL Server MVP, es****@sommarskog.se

Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pro...ads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodinf...ons/books.mspx