473,722 Members | 2,216 Online
Bytes | Software Development & Data Engineering Community
+ Post

Home Posts Topics Members FAQ

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

Dec 5 '05 #1
12 3986
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, 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*


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, es****@sommarsk og.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
Dec 6 '05 #2
Thanks alot

Dec 7 '05 #3
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

Dec 10 '05 #4
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.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, es****@sommarsk og.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
Dec 10 '05 #5
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

Dec 11 '05 #6
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****@sommarsk og.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
Dec 11 '05 #7
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

Dec 11 '05 #8
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, es****@sommarsk og.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
Dec 11 '05 #9
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

Dec 13 '05 #10

This thread has been closed and replies have been disabled. Please start a new discussion.

Similar topics

5
3666
by: Colman | last post by:
Howdy all! I guess I'm a newbie, because I am stumped (or maybe just too durned tired). Here's what I got... CREATE TABLE `nodecat_map` ( `nodecat_id` mediumint(8) unsigned NOT NULL auto_increment, `nodecat_cat_id` mediumint(8) unsigned NOT NULL default '0', `nodecat_node_id` mediumint(8) unsigned NOT NULL default '0', PRIMARY KEY (`nodecat_id`),
12
3978
by: Jeff Lanfield | last post by:
First of all, I apologize if coalescing is not the right term to describe my problem. I have a tree where each node has the same set of attributes (is the same entity) but child nodes should inherit attribute values from parent node. for example, say I have the following table: (nodeId int , color varchar, phone varchar) with two rows 5, "GREEN", "555-1212"
1
3076
by: Hazz | last post by:
I have 5 tables in SQL Server. Each with the following design and a sample chain of the relationships from the root (WRL - World) UUS is the 'Code' of the first table and it is the 'Parent' value of the second table, etc. Parent varchar 3 Name varchar 60 Code varchar 3 WRL United States UUS <- UUS California UCA <- UCA North Coast UNC <- UNC
6
2174
by: jwvai316 | last post by:
I don't really know how to say it so I just say it a nested linklist. How do you make LinkLists inside LinkList? Can anyone help me for this? I think an example program will help me a lot. thank you.
1
6355
by: Random | last post by:
I don't understand why this error is happening when I try to load my xml into my DataSet. "Cannot add a nested relation or an element column to a table containing a SimpleContent column." Can anyone help direct me towards figuring out the cause. Yes, the xml is big, but not too complex.
1
2024
by: Joseph Scoccimaro | last post by:
I am currently working on a project for school dealing with accessing the DOM. I am trying to get access to inner tables that are nested with in other tables. Currently I am able to get all parent tables without a problem. When I try to get an inner table through the child nodes it says that the tr elements of the parent table are undefined. Posted is the code I am using: function nestedTableAnalysis() {
5
2983
by: BMeyer | last post by:
I have been losing my mind trying to parse an XML document (with nested child elements, not all of which appear in each parent node) into a DataGrid object. What I want to do is "flatten" the XML document into a text document with a single row for each parent node (that has all of the values from all of the child nodes for that row) The DataView within VS 2005 IDE displays my 15 or so child tables - and knows that some parent rows...
3
2468
by: =?Utf-8?B?R3JlZyBTdGV2ZW5z?= | last post by:
I am connecting to an Oracle database using an OleDbConnection. I am using DataReader objects to get query results. However, this limits me to only having one reader open at a time, which is a problem for one of the operations I am doing. I have a table with hierarhical data (only 3 levels deep) that I would like to parse through in a depth-first search. Each row in the table in the database has a Name and a ParentName, and I'm doing...
1
11808
by: =?Utf-8?B?SmVyZW15X0I=?= | last post by:
I am working on an order entry program and have a question related to deserializing nodes with nested elements. The purchase order contains multiple line items which I select using an XmlNodeList. I am trying to deserialize the nodes using a foreach as follows: foreach(XmlNode lineItem in LineItemsNodeList) An abbreviated example of the nested lineItem node looks like this:
0
1183
petepell
by: petepell | last post by:
Hello all, I am developing an application in VB 2008 that works with a SQL2005 DB to store and manipulate employee data. In one section of the app I want to be able to show a treeview of the hierarchy as a whole showing a nested hierarchy of who reports to who such that when it is first loaded, all you will see is the top guy in the company with a plus next to his name. Opening that will reveal his first level reports and opening those will...
0
9381
Oralloy
by: Oralloy | last post by:
Hello folks, I am unable to find appropriate documentation on the type promotion of bit-fields when using the generalised comparison operator "<=>". The problem is that using the GNU compilers, it seems that the internal comparison operator "<=>" tries to promote arguments from unsigned to signed. This is as boiled down as I can make it. Here is my compilation command: g++-12 -std=c++20 -Wnarrowing bit_field.cpp Here is the code in...
0
9234
jinu1996
by: jinu1996 | last post by:
In today's digital age, having a compelling online presence is paramount for businesses aiming to thrive in a competitive landscape. At the heart of this digital strategy lies an intricately woven tapestry of website design and digital marketing. It's not merely about having a website; it's about crafting an immersive digital experience that captivates audiences and drives business growth. The Art of Business Website Design Your website is...
1
9151
by: Hystou | last post by:
Overview: Windows 11 and 10 have less user interface control over operating system update behaviour than previous versions of Windows. In Windows 11 and 10, there is no way to turn off the Windows Update option using the Control Panel or Settings app; it automatically checks for updates and installs any it finds, whether you like it or not. For most users, this new feature is actually very convenient. If you want to control the update process,...
1
6680
isladogs
by: isladogs | last post by:
The next Access Europe User Group meeting will be on Wednesday 1 May 2024 starting at 18:00 UK time (6PM UTC+1) and finishing by 19:30 (7.30PM). In this session, we are pleased to welcome a new presenter, Adolph Dupré who will be discussing some powerful techniques for using class modules. He will explain when you may want to use classes instead of User Defined Types (UDT). For example, to manage the data in unbound forms. Adolph will...
0
5994
by: conductexam | last post by:
I have .net C# application in which I am extracting data from word file and save it in database particularly. To store word all data as it is I am converting the whole word file firstly in HTML and then checking html paragraph one by one. At the time of converting from word file to html my equations which are in the word document file was convert into image. Globals.ThisAddIn.Application.ActiveDocument.Select();...
0
4501
by: TSSRALBI | last post by:
Hello I'm a network technician in training and I need your help. I am currently learning how to create and manage the different types of VPNs and I have a question about LAN-to-LAN VPNs. The last exercise I practiced was to create a LAN-to-LAN VPN between two Pfsense firewalls, by using IPSEC protocols. I succeeded, with both firewalls in the same network. But I'm wondering if it's possible to do the same thing, with 2 Pfsense firewalls...
0
4762
by: adsilva | last post by:
A Windows Forms form does not have the event Unload, like VB6. What one acts like?
1
3207
by: 6302768590 | last post by:
Hai team i want code for transfer the data from one system to another through IP address by using C# our system has to for every 5mins then we have to update the data what the data is updated we have to send another system
2
2600
muto222
by: muto222 | last post by:
How can i add a mobile payment intergratation into php mysql website.

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.