Nested Sets Tree Structure

Hi,
I need to store a tree using nested sets (http://www.codeproject.com/
database/nestedsets.asp) in SQL Server 2000.
I've got the database sorted, but ive got a problem reading that
data...
I need to get the data out of the database and into an XML structure
in C# (if anyones got a java example or similar i can translate it)
Can anyone point me at any code or tutorials on how to do this? I
really can't think of a sensible way of doing it, but there must be!
Cheers
AndrewHi
"trullock@.hotmail.com" wrote:
> Hi,
>
> I need to store a tree using nested sets (http://www.codeproject.com/
> database/nestedsets.asp) in SQL Server 2000.
> I've got the database sorted, but ive got a problem reading that
> data...
> I need to get the data out of the database and into an XML structure
> in C# (if anyones got a java example or similar i can translate it)
> Can anyone point me at any code or tutorials on how to do this? I
> really can't think of a sensible way of doing it, but there must be!
>
> Cheers
> Andrew
>
Check out http://www.perfectxml.com/Articles/XML/ExportSQLXML.asp
and http://sqlxml.org/faqs.aspx?faq=10
John|||On 16 Feb, 08:25, John Bell <jbellnewspo...@.hotmail.com> wrote:
> Hi
>
> "trull...@.hotmail.com" wrote:
> > Hi,
> > I need to store a tree using nested sets (http://www.codeproject.com/
> > database/nestedsets.asp) in SQL Server 2000.
> > I've got the database sorted, but ive got a problem reading that
> > data...
> > I need to get the data out of the database and into an XML structure
> > in C# (if anyones got a java example or similar i can translate it)
> > Can anyone point me at any code or tutorials on how to do this? I
> > really can't think of a sensible way of doing it, but there must be!
> > Cheers
> > Andrew
> Check outhttp://www.perfectxml.com/Articles/XML/ExportSQLXML.asp
> andhttp://sqlxml.org/faqs.aspx?faq=10
> John
Thanks, that looks like what I need :)
I've made another post about nested sets and adjacency lists if you
happen to know anything about them too,
Thanks
Andrew|||Hi
"trullock@.hotmail.com" wrote:
> On 16 Feb, 08:25, John Bell <jbellnewspo...@.hotmail.com> wrote:
> > Hi
> >
> >
> >
> > "trull...@.hotmail.com" wrote:
> > > Hi,
> >
> > > I need to store a tree using nested sets (http://www.codeproject.com/
> > > database/nestedsets.asp) in SQL Server 2000.
> >
> > > I've got the database sorted, but ive got a problem reading that
> > > data...
> >
> > > I need to get the data out of the database and into an XML structure
> > > in C# (if anyones got a java example or similar i can translate it)
> >
> > > Can anyone point me at any code or tutorials on how to do this? I
> > > really can't think of a sensible way of doing it, but there must be!
> >
> > > Cheers
> >
> > > Andrew
> >
> > Check outhttp://www.perfectxml.com/Articles/XML/ExportSQLXML.asp
> > andhttp://sqlxml.org/faqs.aspx?faq=10
> >
> > John
>
> Thanks, that looks like what I need :)
> I've made another post about nested sets and adjacency lists if you
> happen to know anything about them too,
> Thanks
> Andrew
Joe Celko's book "Trees and Hierarchies in SQL for Smarties" ISBN
1-55860-920-2 has just about everything you need for nested sets and you may
want to Google for his posts.
The parent will be the node with the maximum left value less than the
current node's left value and the minimum right values which is greater than
the current node's right value!
John|||On 16 Feb, 10:05, John Bell <jbellnewspo...@.hotmail.com> wrote:
> The parent will be the node with the maximum left value less than the
> current node's left value and the minimum right values which is greater than
> the current node's right value!
> John
Cool thanks, think I get that.
Is it possible to do that in a single SQL command, or would I have to
use a user defined function like this:
SELECT Node_ID, Left, Right, fn_FindParent(Node_ID) as ParentID FROM
NODES
Im guessing that will be slow :s
Andrew|||Hi Andrew
"trullock@.hotmail.com" wrote:
> On 16 Feb, 10:05, John Bell <jbellnewspo...@.hotmail.com> wrote:
> > The parent will be the node with the maximum left value less than the
> > current node's left value and the minimum right values which is greater than
> > the current node's right value!
> >
> > John
>
> Cool thanks, think I get that.
> Is it possible to do that in a single SQL command, or would I have to
> use a user defined function like this:
> SELECT Node_ID, Left, Right, fn_FindParent(Node_ID) as ParentID FROM
> NODES
> Im guessing that will be slow :s
> Andrew
>
There may be other ways to get the parent, the speed would really be
dependent on the size of the hierarchy.
If you wanted to do this in one query it would be (using the ddl from the
article):
SELECT e.EmployeeID,
e.ParentID,
(SELECT f.EmployeeID
FROM Employee f WHERE f.LeftExtent =(SELECT MAX(g.LeftExtent) FROM Employee g WHERE g.LeftExtent < e.LeftExtent
AND g.RightExtent > e.RightExtent )
AND f.RightExtent =(SELECT Min(g.RightExtent) FROM Employee g WHERE g.RightExtent >
e.RightExtent AND g.LeftExtent < e.LeftExtent )
)
FROM Employee e
As a function:
CREATE Function dbo.fn_GetLevel ( @.LeftExtent int, @.RightExtent int )
RETURNS INT
AS
BEGIN
RETURN ( SELECT f.EmployeeID
FROM Employee f
WHERE f.LeftExtent = (SELECT MAX(g.LeftExtent) FROM Employee g WHERE g.LeftExtent <
@.LeftExtent AND g.RightExtent > @.RightExtent )
AND f.RightExtent = (SELECT Min(g.RightExtent) FROM Employee g WHERE g.RightExtent >
@.RightExtent AND g.LeftExtent < @.LeftExtent )
)
END
SELECT e.EmployeeID,
e.ParentID,
dbo.fn_GetLevel ( e.LeftExtent, e.RightExtent )
FROM Employee e
John