T-SQL HIERACHID.ISDESCENDANTOF

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

NodeIdPathnodeidNodeIdDisplayPath
/1/2/0x5B40爷爷 -> 父亲 -> 次子
/1/2/1/0x5B56爷爷 -> 父亲 -> 次子 -> 长女
/1/2/2/0x5B5A爷爷 -> 父亲 -> 次子 -> 次孙