HierarchyId method and GetAncestor In SQL
The hierarchyid data type is a variable-length, system data type. A value of the hierarchyid data type represents a position in a tree hierarchy.
A slash starts the representation, and a path that only visits the root is represented by a single slash. For levels underneath the root, each label is encoded as a sequence of integers separated by dots. Comparison between children is performed by comparing the integer sequences separated by dots in dictionary order. Each level is followed by a slash. Therefore a slash separates parents from their children. For example, the following are valid hierarchyid paths of lengths 1, 2, 2, 3, and 3 levels respectively:
//1//0.4.-6//1/5//0.1/0.2/
Data type conversion
The hierarchyid data type can be converted to other data types as follows:
Use the ToString() method to convert the hierarchyid value to the logical representation as a nvarchar(4000) data type.
Use Read () and Write () to convert hierarchyid to varbinary.
To transmit hierarchyid parameters through SOAP first cast them as strings.
GetAncestor
Returns a hierarchyid representing the nth ancestor of this.
Syntax
child.GetAncestor ( n )Finding the child nodes of a parent
GetAncestor(1) returns the employees that have david0 as their immediate ancestor (their parent). The following example uses GetAncestor(1).
DECLARE @CurrentEmployee hierarchyid
SELECT @CurrentEmployee = OrganizationNode FROM HumanResources.Employee
WHERE LoginID = ‘adventure-works\david0’
SELECT OrganizationNode.ToString() AS Text_OrgNode,*
FROM HumanResources.Employee
WHERE OrganizationNode.GetAncestor(1) = @CurrentEmployeeReturning the grandchildren of a parent
GetAncestor(2) returns the employees that are two levels down in the hierarchy from the current node. These employees are the grandchildren of the current node. The following example uses GetAncestor(2).
DECLARE @CurrentEmployee hierarchyid
SELECT @CurrentEmployee = OrganizationNode FROM HumanResources.Employee
WHERE LoginID = ‘adventure-works\david0’
SELECT OrganizationNode.ToString() AS Text_OrgNode,*
FROM HumanResources.Employee
WHERE OrganizationNode.GetAncestor(2) = @CurrentEmployeeReturning the current row
To return the current node by using GetAncestor(0), execute the following code.
DECLARE @CurrentEmployee hierarchyid
SELECT @CurrentEmployee = OrganizationNode FROM HumanResources.Employee
WHERE LoginID = ‘adventure-works\david0’
SELECT OrganizationNode.ToString() AS Text_OrgNode,*
FROM HumanResources.Employee
WHERE OrganizationNode.GetAncestor(0) = @CurrentEmployee
