Retrieve Data from SQL Server in an hierarchical XML.

Hi,
I have a table containing hierarchical data and I would like to retrieve it
form SQL Server as hierarchical XML so i can use it directly for binding wit
h
e.g. the ASP.NET2 Treeview
E.g table:Nodes
Table:Nodes
id parent Name
1 0 Node1
2 1 Node11
3 1 Node12
4 2 Node111
Format to be retrieved:
<nodes>
<node value="1" text="node1">
<node value="2" text="node11">
<node value="4" text="node111"></node>
</node
<node value="3" text="node12"></node>
</node>
</nodes>
- Raoul Jacobs
The nature of developping is sharing knowledge.Raoul,
First, SQLXML will not provide you with such output. You will need to take a
standard resultset and convert it to XML.
Second, if you want to retreive heirarchical data easily, then you need to
store your heirarchy properly. As you can see from your experience, the
ID/ParentID doesn't work out so well.
Search for SQL Nested Sets on google. Also, I would also recommend getting
either SQL For Smarties or Trees and Heirarchies in SQL (both by Celko).
Otherwise, your SOL and will need to develop a horribly ugly trigger or
other equally bad middle-tier code.
-- Alex Papadimoulis
"JaRa" wrote:

> Hi,
> I have a table containing hierarchical data and I would like to retrieve i
t
> form SQL Server as hierarchical XML so i can use it directly for binding w
ith
> e.g. the ASP.NET2 Treeview
> E.g table:Nodes
> Table:Nodes
> id parent Name
> 1 0 Node1
> 2 1 Node11
> 3 1 Node12
> 4 2 Node111
> Format to be retrieved:
> <nodes>
> <node value="1" text="node1">
> <node value="2" text="node11">
> <node value="4" text="node111"></node>
> </node
> <node value="3" text="node12"></node>
> </node>
> </nodes>
> --
> - Raoul Jacobs
> The nature of developping is sharing knowledge.