Retrieve subtree of current element hierarchy

SELECT Name FROM NuGreen.Asset.ElementHierarchy
WHERE Path LIKE N'\NuGreen\Houston\Cracking Process\Equipment\%'
/*"Path LIKE ..." condition restricts hierarchy traversal to subtree*/