Website Navigation Hierarchy With SQL Server 2005

Hi

I'm trying to convert some verbose SQL Server 2000 T-SQL code that uses
lots temp tables and the like into a SQL Server 2005 only version,
possibly using CTE.

What I want to achieve is a menu like that on
http://www.cancerline.com/cancerlin...9801_6_3_3.aspx

Notice how you have the top level menu items, and then child nodes
expanded down to the low level page that link sends you to.

Table sql:

CREATE TABLE [dbo].[NavigationNode](
[NodeId] [int] primary key nonclustered,
[Text] [nvarchar](150) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[AlternativeText] [nvarchar](250) COLLATE SQL_Latin1_General_CP1_CI_AS
NULL,
[Level] [int] NULL,
[ParentNodeId] [int] constraint fk_parent_navnode
foreign key references [NavigationNode] ([NodeId])
)

Table data:

1HomeNULL1NULL
2SolutionsNULL11
3Solutions child1NULL22
4Solutions child2NULL22
5ContactNULL11
6solutions child1 child1NULL33
7solutions child1 child2NULL33
8solutions child1 child3NULL33
9solutions child1 child4NULL33
10contact child1NULL25

I have started to write some code with the common table expression
syntax:

declare @root int;
set @root = 2;

WITH Nav([NodeId],[ParentNodeId], [Text], [Level]) AS
(
SELECT [NodeId], [ParentNodeId], [Text], [Level]
FROM [dbo].[NavigationNode]
WHERE [ParentNodeId] = @root

UNION ALL

SELECT n1.[NodeId], n1.[ParentNodeId], n1.[Text], n1.[Level]
FROM [dbo].[NavigationNode] n1
INNER JOIN Nav n2
ON n1.[ParentNodeId] = n2.[NodeId]

)
SELECT *
FROM Nav

Which returns:

32Solutions child12
42Solutions child22
63solutions child1 child13
73solutions child1 child23
83solutions child1 child33
93solutions child1 child43

However I would prefer to the childnode, and then get parents of that
child recursively. Doing that would leave me with a result set that
could add the top level menu items to and have all the data required.
Any help is greatly appreciated.

-Brian