Given a table with a
hierarchyid type column, how do you write a query to return all rows that are ancestors of a specific node?
There is an
IsDescendantOf() function, which is perfect for getting the children, but there’s no corresponding
IsAncestorOf() function to return ancestors.
Here is the query which will return all the ancestors of a records whose hierarchyid is passed.
DECLARE @hierarchyidValue AS hierarchyid SET @hierarchyidValue = 0x5AD6D0 SELECT Name From TableName T WHERE @hierarchyidValue.IsDescendantOf(T.Hierarchy) = 1