DECLARE @ParentNodeId hierarchyid
SELECT @ParentNodeId = NodeId
FROM family
WHERE familyId = 113
SELECT NodeId.ToString() AS NodeIdPath, nodeid,dbo.fnGetFullDisplayPath(NodeId) AS NodeIdDisplayPath
FROM family
WHERE NodeId.IsDescendantOf(@ParentNodeId) = 1
ORDER BY NodeLevel, NodeId
NodeIdPath nodeid NodeIdDisplayPath
/1/2/ 0x5B40 爷爷 -> 父亲 -> 次子
/1/2/1/ 0x5B56 爷爷 -> 父亲 -> 次子 -> 长女
/1/2/2/ 0x5B5A 爷爷 -> 父亲 -> 次子 -> 次孙