Posted by coosa on 12/06/05 00:10

Dear all,

I have table called CATEGORY, which is defined as follows:

CREATE TABLE CATEGORY
(
CATEGORY_ID INTEGER IDENTITY(1,1) NOT NULL,
CATEGORY_NAME VARCHAR(40) NOT NULL CONSTRAINT
UC__CATEGORY__CATEGORY_NAME UNIQUE,
PARENT_CATEGORY_ID INTEGER,
CATEGORY_ICON IMAGE,
DEPTH INTEGER,
CONSTRAINT PK__CATEGORY PRIMARY KEY (CATEGORY_ID)
)

Supposly, the following snap shot was taken later:

==============================================================
| CATEGORY_ID | CATEGORY_NAME | PARENT_CATEGORY_ID | DEPTH |
==============================================================
| 1 | PC | NULL | 1 |
--------------------------------------------------------------
| 2 | Networks | 1 | 2 |
--------------------------------------------------------------
| 3 | Audio | 1 | 2 |
--------------------------------------------------------------
| 4 | Video | 1 | 2 |
--------------------------------------------------------------
| 5 | TV Cards | 4 | 3 |
--------------------------------------------------------------
| 6 | Graphics Cards | 4 | 3 |
--------------------------------------------------------------
| 7 | AGP | 6 | 4 |
--------------------------------------------------------------
| 8 | PCI | 6 | 4 |
--------------------------------------------------------------
| 9 | Input Devices | 1 | 2 |
--------------------------------------------------------------

and I'd like to create out of this hierarchy the following desired XML
file:

<?xml version="1.0" encoding="utf-8" ?>
<Hardware>
<Catgeory name="PC" id="1">
<Catgeory name="Networks" id="2" />
<Catgeory name="Audio" id="3" />
<Catgeory name="Video" id="4">
<Catgeory name="TV Cards" id="5" />
<Catgeory name="Graphics Cards" id="6">
<Catgeory name="AGP" id="7" />
<Catgeory name="PCI" id="8" />
</Category>
</Category>
<Catgeory name="Input Devices" id="9" />
</Catgeory>
</Hardware>

The reason for this file is that it will be a datasource of the
TreeView Control new in asp.net 2.0.

Now, programmateiclally using C#.net i started using the XmlDocument,
XmlTextWriter and XmlTextReader Namespaces and started using susing
recurrsion to genearete this desired XML file out of the records in the
snapshot, but ...

Is there an easy way of doing this using SqlServer 2005 with the new
datatype XML?
*Any hint would also be ok*

Best regards

• Next in forum: Re: 1 SP with dynamic input parameters and multiple rows as the source of the query
• Prev in forum: Re: Getting data from multiple rows into one column
• Thread view: Nested Nodes in XML from a table

[Reply to this message]