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:


==============================================================
Show quoteHide quoteand 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>

How to do it?

Thanks in Advance
Rajesh

Can you give the insert scripts for the sample data

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


Show quoteHide quote

Problems while migrating from MSSQL 2000 to 2005 - select... into fails in a trigger - please help.
sp question re: dynamic sql
Crazy Row Numbering Poblem
can't delete a row from primary table - is there an SP for this?
Stored Proc SQL 2000 and Dates