I'm going to let you in on a little secret: I love the hierarchyid datatype! It's not often that I get to use it, but when I can, it solves the problem so elegantly. So when this Stack Overflow question came up, I couldn't help myself. The question boils down to: given a hierarchyid, is there a way to find all the ancestors of it? Before we answer that question (spoiler alert: the answer is "yes we can!"), let's learn to walk before we run.
HierarchyID is a CLR datatype (just like the geospatial and xml types). As such, it defines not only data, but behavior as well. The hierarchyid method reference can be found here. But for our purposes, we're only concerned with two methods: GetLevel() and GetAncestor(n). GetLevel() essentially tells us how deep in the tree this particular node is. So, if I have a hierarchyid defined as @h = '/1/2/3/4/5/', @h.GetLevel() returns 5 because the leaf node is five steps away from the root. GetAncestor(n) returns the nth ancestor of the given hierarchyid. So, to reuse our hierarchyid of @h = '/1/2/3/4/5/' from before, @h.GetAncestor(1) returns '/1/2/3/4/', @h.GetAncestor(2) returns '/1/2/3/' and so on.
So now back to the Stack Overflow question. Out of the box, there's no way to get all the ancestors for a given hierarchyid. But, it's not too hard to roll your own if you have a numbers table. Here's the code:
alter function dbo.GetAllAncestors(@h hierarchyid, @ReturnSelf bit) returns table as return select @h.GetAncestor(n.Number) as h from dbo.Numbers as n where n.Number <= @h.GetLevel() or (@ReturnSelf = 1 and n.Number = 0) union all select @h where @ReturnSelf = 1 go
By way of explanation, all I'm doing is returning the numbers 1-(@h.GetLevel()) from a tally table and then using those tally values as the argument to GetAncestor. As a convenience, I've also specified an argument to the function that says whether you consider the passed in hierarchyid to be its own ancestor (I've been in applications where that made sense).
So there you go. A little hierarchyid fun.