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__C ATEGORY_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, programmateicla lly 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]

Re: Nested Nodes in XML from a table

coosa (coosa76@gmail. com) writes:[color=blue]
> 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, programmateicla lly 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*[/color]

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.directRepor ts
go
create function dbo.directRepor ts
(@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.directRepo rts(e.employeeI D,0)
from HumanResources. Employee e
join person.contact c
on e.contactID = c.contactID
where e.managerID is null
order by lastName,firstN ame
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.directRepo rts(e.employeeI D,@depth + 1)
from HumanResources. Employee e
join person.contact c
on e.contactID = c.contactID
where e.managerID = @managerID
order by lastName,firstN ame
for xml path('employee' ),type)
end
return @x
end
go

select dbo.directRepor ts(null,0)
from humanresources. employee
where managerID is null
for xml path(''),root(' employees'),typ e
go

I hope you are able to work from this.



--
Erland Sommarskog, SQL Server MVP, esquel@sommarsk og.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

[coosa]

Re: Nested Nodes in XML from a table

Thanks alot

[coosa]

Re: Nested Nodes in XML from a table

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]

Re: Nested Nodes in XML from a table

coosa (coosa76@gmail. com) writes:[color=blue]
> 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,[/color]

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.directRepo rts(e.employeeI D,0)

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


--
Erland Sommarskog, SQL Server MVP, esquel@sommarsk og.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

[coosa]

Re: Nested Nodes in XML from a table

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('Categor ies'),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.directRepor ts(null)
from category
where parent_category _id is null
for xml path(''),root(' Categories'),ty pe
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]

Re: Nested Nodes in XML from a table

coosa (coosa76@gmail. com) writes:[color=blue]
> 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?[/color]

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@sommarsk og.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

[coosa]

Re: Nested Nodes in XML from a table

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__C ATEGORY_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.DEPT H, 0) + 1
FROM CATEGORY C
JOIN inserted I ON C.CATEGORY_ID = I.CATEGORY_ID
LEFT JOIN CATEGORY P ON I.PARENT_CATEGO RY_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(CATEG ORY_ID, LVL)
SELECT CATEGORY_ID,
@LVL
FROM INSERTED
SELECT @ROWC = @@ROWCOUNT
WHILE @ROWC <> 0
BEGIN
UPDATE C
SET DEPTH = coalesce(P.DEPT H, 0) + 1
FROM CATEGORY C
LEFT JOIN CATEGORY P
ON C.PARENT_CATEGO RY_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_CATEGO RY_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',5 8)
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]

Re: Nested Nodes in XML from a table

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(C ATEGORY_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(C ATEGORY_ID, 0)
from CATEGORY
where PARENT_CATEGORY _ID IS NULL
for xml path('Category' ),root('HARDWAR E'), type
go

The result looks good to me...

--
Erland Sommarskog, SQL Server MVP, esquel@sommarsk og.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

[coosa]

Re: Nested Nodes in XML from a table

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]

Re: Nested Nodes in XML from a table

coosa (coosa76@gmail. com) writes:[color=blue]
> 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?[/color]

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, esquel@sommarsk og.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

[coosa]

Re: Nested Nodes in XML from a table

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]

Re: Nested Nodes in XML from a table

coosa (coosa76@gmail. com) writes:[color=blue]
> 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?[/color]

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.
[color=blue]
> 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?[/color]

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, esquel@sommarsk og.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