How to Select all child elements of a given Node in SqlHierarchyId? I have a Node as /1/2/, Here I want to select all child Nodes of '/1/2/'
Ex /1/2/1/ , /1/2/2/1/.../
A worked example of what Mark's talking about, using IsDescendantOf:

DECLARE @t TABLE ( hId hierarchyId PRIMARY KEY )

INSERT INTO @t VALUES
    ( '/1/' ),
    ( '/1/2/' ),
    ( '/1/2/1/' ),
    ( '/1/2/2/1/' ),
    ( '/3/' ),
    ( '/3/4/' )

DECLARE @hId hierarchyId = '/1/2/'

SELECT *, hId.ToString() hId_string
FROM @t
WHERE hId.IsDescendantOf(@hId) = 1