List menu hierarchy using SQL Server hierarchyid

Question

I try to build a menu using the datatype hierarchyid.

I have the root node and the current selected node. now I want to list of all elements that are related wetween root and selected node AND there siblings.

I get all related elements with following sql query

DECLARE @rootNode hierarchyid, @selectedNode hierarchyid

SELECT @rootNode = MenuNode FROM CMS_Menu WHERE MenuItemID = 3;

SELECT @selectedNode = MenuNode FROM CMS_Menu WHERE MenuItemID =15;

SELECT CMS_Menu.MenuNode  
FROM CMS_Menu
WHERE @selectedNode.IsDescendantOf(MenuNode) = 1 /*all related elements*/
AND MenuNode.GetLevel() >= @rootNode.GetLevel() /*nothing below root*/

Now I have to do something like MenuNode.GetAncestor(1) = result for each row in the query above.

Does anyone have an idea how to get this in a sql query?

Thanks : )

Answer 1

Not entirely sure I understand the question but could you not do something like the following with the WHERE clause:

WHERE @selectedNode.IsDescendantOf(MenuNode.GetAncestor(1)) = 1

Tom