How to find ALL descendants using HierarchyID for SQL Server

Question

I need to find all descendants of a category using HierarchyID for SQL Server.

I know how to find direct children but I would like to find children of children of children and so on.

Is there a way to do this using the HierarchyID?

Answer 1

If you have the root of the tree you want, can't you just use:

DECLARE @root hierarchyID;

SELECT @root = col
FROM yourTable
WHERE [whatever uniquely identifies this row]

SELECT *
FROM yourTable
WHERE col.IsDescendantOf(@root) = 1