Need advice for getting parent hierarchy path

Hi,

I'm using HierarchyID for creating a products category in my application. As
I allow users to create sub categories, I want to display the entire path of
the parent category so that they can see where they're inserting the new node.

For example: I want to display Root > Electronics > Digital Media Players

Obviously, I need to start by getting this info. If I pass the HierarchyID
of the parent, how do I get the whole path?
--
Thanks,

Sam

Here is one example:

CREATE TABLE Foo (
category_hid HIERARCHYID NOT NULL PRIMARY KEY,
category_name VARCHAR(30));

INSERT INTO Foo VALUES(hierarchyid::GetRoot(), 'Root');

DECLARE @hid HIERARCHYID;
SELECT @hid = hierarchyid::GetRoot()
FROM Foo;

INSERT INTO Foo VALUES(@hid.GetDescendant(NULL, NULL), 'Electronics');

SELECT @hid = category_hid
FROM Foo
WHERE category_name = 'Electronics';

INSERT INTO Foo VALUES(@hid.GetDescendant(NULL, NULL), 'Digital Media
Players');

WITH Hierarchy AS (
SELECT category_hid, category_name, CAST(category_name AS
VARCHAR(MAX)) AS category_path
FROM Foo
WHERE category_hid = hierarchyid::GetRoot()
UNION ALL
SELECT F.category_hid, F.category_name, H.category_path + ' > ' +
F.category_name
FROM Foo AS F
JOIN Hierarchy AS H
ON F.category_hid.GetAncestor(1) = H.category_hid)
SELECT category_hid, category_name, category_path
FROM Hierarchy;

/*

category_hid category_name category_path
-------------- ------------------------------
--------------------------------------------
0x Root Root
0x58 Electronics Root > Electronics
0x5AC0 Digital Media Players Root > Electronics >
Digital Media Players

*/

--
Plamen Ratchev
http://www.SQLStudio.com