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
12  3994 
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
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 (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
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
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
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
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
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
This thread has been closed and replies have been disabled. Please start a new discussion. Similar topics | |
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`),
|
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"
|
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
|
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.
|
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.
| | |
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()
{
|
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...
|
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...
|
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:
|
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...
|
by: marktang |
last post by:
ONU (Optical Network Unit) is one of the key components for providing high-speed Internet services. Its primary function is to act as an endpoint device located at the user's premises. However, people are often confused as to whether an ONU can Work As a Router. In this blog post, we’ll explore What is ONU, What Is Router, ONU & Router’s main usage, and What is the difference between ONU and Router. Let’s take a closer look !
Part I. Meaning of...
| | |
by: Hystou |
last post by:
Most computers default to English, but sometimes we require a different language, especially when relocating. Forgot to request a specific language before your computer shipped? No problem! You can effortlessly switch the default language on Windows 10 without reinstalling. I'll walk you through it.
First, let's disable language synchronization. With a Microsoft account, language settings sync across devices. To prevent any complications,...
|
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...
|
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...
|
by: agi2029 |
last post by:
Let's talk about the concept of autonomous AI software engineers and no-code agents. These AIs are designed to manage the entire lifecycle of a software development project—planning, coding, testing, and deployment—without human intervention. Imagine an AI that can take a project description, break it down, write the code, debug it, and then launch it, all on its own....
Now, this would greatly impact the work of software developers. The idea...
|
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...
|
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();...
| | |
by: adsilva |
last post by:
A Windows Forms form does not have the event Unload, like VB6. What one acts like?
|
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
| |
