SQL Server HierarchyId in Production – Is HierarchyId Reliable?

To get "all parent nodes of a given node":

select *, position.GetAncestor(1), position.GetAncestor(1).ToString()
from employee
where position=hierarchyid::Parse('/3/1/')

EmpId  EmpName       Position  (No column name)  (No column name)
5      VP Financing  0x7AC0    0x78              /3/

but there will only ever be one due to the nature of hierarchies.

If you really want to get all immediate children nodes of a given node:

select * 
from employee 
where position.IsDescendantOf(hierarchyid::Parse('/3/1/'))=1
      and position.GetLevel()=hierarchyid::Parse('/3/1/').GetLevel()+1

EmpId  EmpName              Position
6      Accounts Receivable  0x7AD6
10     Accounts Payable     0x7ADA

-- EDIT

I see that you want all ancestor nodes. Perhaps try an approach like this:

select * 
from employee
where hierarchyid::Parse('/3/1/2/1/').IsDescendantOf(Position) = 1

or

select * from employee
where ( select position 
        from employee 
        where empname='Accountant 4' ).IsDescendantOf(Position) = 1

here is a CTE method for comparison:

with w as ( select * from employee where empname='Accountant 4'
            union all
            select e.*
            from employee e join w on(w.position.GetAncestor(1)=e.Position) )
select * from w;

The function to use is GetReparentedValue but when using only GetReparentedValue the tree may end up in an "inconsistent" state.

Here is some code provided by Microsoft that takes care of that. Moving subtrees.

I guess that related to this is Enforcing a tree. It uses a calculated column for parent id that does a self join to the PK.