The ToString Method
The preceding output shows the hexadecimal representation of each node’s encoded hierarchyid
value, which is hardly useful to you. To view this as human-readable
text that conveys each node’s path within the tree, change the SELECT statement in Example 3 to call the ToString method on the hierarchyid values, as shown here:
SELECT
@Root.ToString() AS Root,
@Child1.ToString() AS Child1,
@Child2.ToString() AS Child2,
@Child3.ToString() AS Child3,
@Child4.ToString() AS Child4,
@Grandchild1.ToString() AS Grandchild1,
@Grandchild2.ToString() AS Grandchild2
GO
Root Child1 Child2 Child3 Child4 Grandchild1 Grandchild2
---- ------ ------ ------ ------ ----------- -----------
/ /1/ /2/ /1.1/ /0/ /1/1/ /1/2/
(1 row(s) affected)
You can make several observations from this output. First, you can see that the ToString method decodes the hierarchyid value
into a slash-delimited string. Each slash designates a level, so
subtracting one from the number of slashes indicates what the
zero-based level position of each node is (the same value that would be
returned by the GetLevel method). Root is the only node with one slash, representing level zero. The Child1, Child2, Child3, and Child4 nodes have two slashes, representing level one. The Grandchild1 and Grandchild2 nodes, naturally, have three slashes representing level two.
You can also see how the two parameters that were passed to the GetDescendant method influenced the assignment of hierarchyid values. Root of course was assigned by hierarchyid::GetRoot() to get the tree started, as you saw in Example 2. The hierarchyid for each of the four child nodes beneath the root parent was assigned by invoking the GetDescendant
method on the root. And the ordinal position of each child node among
siblings of the same parent was determined by the two parameters passed
to GetDescendant.
Child1 is the first node defined beneath the root, as follows:
SET @Child1 = @Root.GetDescendant(NULL, NULL)
Because
there are not yet any other child nodes beneath the root, there is no
position relative to other child nodes that can be specified. You
therefore pass NULL as both positional values to GetDescendant. The result is a hierarchyid represented as /1/, which refers to the one and only child node beneath the root.
Child2 is created as the second node beneath the root, which you want to be placed after Child1. Think of this as inserting Child2 between Child1 and “the end,” which you pass as the two parameters Child1 and NULL, respectively, when calling GetDescendant for Child2:
SET @Child2 = @Root.GetDescendant(@Child1, NULL)
The result is a hierarchyid represented as /2/, which refers to the second child node beneath the root, to the right of Child1. You then apply the same principle to create Child3 as a third child node beneath the root, except that you want this child to be positioned between Child1 and Child2 rather than being appended as Child2 was. You explicitly request such ordering when calling GetDescendant for Child3, as shown here:
SET @Child3 = @Root.GetDescendant(@Child1, @Child2)
This code assigns a hierarchyid value to Child3 between Child1 and Child2. Because you specified a position between Child1 and Child2 with hierarchyid values of /1/ and /2/, respectively, SQL Server assigns to Child3 a hierarchyid value represented by ToString as /1.1/ (that is, after /1/ and before /2/).
Last, you create Child4 as another child beneath the root, this time inserted in front of all the other siblings (that is, before Child1). Think of this as inserting Child4 between “the beginning” and Child1, which you pass as the two parameters NULL and Child1, respectively, when calling GetDescendant for Child4 as follows:
SET @Child4 = @Root.GetDescendant(NULL, @Child1)
The result is a hierarchyid represented as /0/ assigned for Child4, the last child node added beneath the root, to the left of Child1. If you continued adding more nodes to the left of Child4, their ToString representations would begin showing negative values, such as /-1/, /-2/, and so on.
Adding grandchild (level-two) nodes beneath Child1 is achieved using the very same approach:
SET @Grandchild1 = @Child1.GetDescendant(NULL, NULL)
This code assigns a hierarchyid value of /1/1/ to Grandchild1, the first child node beneath Child1.
Once again, being the first child of a parent, there are no existing
sibling nodes that you can refer to with respect to the position at
which Grandchild1 should be inserted, and so the only possible values to pass as parameters to GetDescendant are NULL and NULL. Last, you add Grandchild2 as a second child beneath Child1, positioned after its sibling node Grandchild1, as shown here:
SET @Grandchild2 = @Child1.GetDescendant(@Grandchild1, NULL)
Now let’s get back to the hierarchical
employee table. After Dave has been added at the root, the level-one
employee nodes that you will now add beneath him are assigned hierarchyid values returned by a call to GetDescendant on Dave. The first level-one employee you add is Amy, with an employee ID of 46, as shown in Example 4:
Example 4. Adding the first child node beneath the root of the hierarchy.
DECLARE @Manager hierarchyid
SELECT @Manager = NodeId
FROM Employee
WHERE EmployeeId = 6
INSERT INTO Employee
(NodeId, EmployeeId, EmployeeName, Title)
VALUES
(@Manager.GetDescendant(NULL, NULL), 46, 'Amy', 'Marketing Specialist')
GO
Let’s examine this code closely. You first need to get the hierarchyid value of the parent (Dave, with employee ID 6), which you retrieve from the table and store in the @Manager variable (also declared as a hierarchyid data type, of course). Then you can insert Amy as the first child row by specifying a hierarchyid value returned by the GetDescendant method invoked on @Manager. Because there are no other child nodes yet beneath Dave, the only positional parameter values that can be passed to GetDescendant are NULL and NULL. Let’s now view both rows in the table, including a NodeIdPath column that translates the encoded hierarchyid values using the ToString method.
SELECT NodeId.ToString() AS NodeIdPath, *
FROM Employee
GO
NodeIdPath NodeId NodeLevel EmployeeId EmployeeName Title
---------- ------ --------- ---------- ------------ --------------------
/ 0x 0 6 Dave Marketing Manager
/1/ 0x58 1 46 Amy Marketing Specialist
(2 row(s) affected)
Sure enough, Amy has been added as the first level-one child node beneath Dave, as indicated by the hierarchyid value /1/ assigned to her by GetDescendant.
This demonstrates that adding a child node requires a SELECT to first obtain the hierarchyid value of the desired parent before performing an INSERT to actually add the new row. In this manner, the new row can be assigned a hierarchyid value based on the result of invoking GetDescendant on the hierarchyid
value of its parent. We presented the code as two separate statements
to make this process clear. However, you could have just as effectively
written this as a single statement without requiring the temporary @Manager variable to hold the parent’s hierarchyid by embedding the SELECT statement as a subquery within the VALUES clause of the INSERT statement, as follows:
INSERT INTO Employee
(NodeId, EmployeeId, EmployeeName, Title)
VALUES (
(SELECT NodeId
FROM Employee
WHERE EmployeeId = 6). GetDescendant(NULL, NULL),
46,
'Amy',
'Marketing Specialist')
GO
You’ll probably want to simplify the process for adding more employees. In Example 5, the uspAddEmployee stored procedure encapsulates logic that retrieves the hierarchyid of any desired parent and invokes GetDescendant on it to obtain the appropriate hierarchyid for the new employee.
Example 5. Creating a stored procedure to automatically insert child nodes in a hierarchy.
CREATE PROC uspAddEmployee(
@ManagerId int,
@EmployeeId int,
@EmployeeName varchar(20),
@Title varchar(20))
AS
BEGIN
DECLARE @ManagerNodeId hierarchyid
DECLARE @LastManagerChild hierarchyid
DECLARE @NewEmployeeNodeId hierarchyid
-- Get the hierarchyid of the desired parent passed in to @ManagerId
SELECT @ManagerNodeId = NodeId
FROM Employee
WHERE EmployeeId = @ManagerId
SET TRANSACTION ISOLATION LEVEL SERIALIZABLE
BEGIN TRANSACTION
-- Get the hierarchyid of the last existing child beneath the parent
SELECT @LastManagerChild = MAX(NodeId)
FROM Employee
WHERE NodeId.GetAncestor(1) = @ManagerNodeId
-- Assign a new hierarchyid positioned at the end of any existing siblings
SELECT @NewEmployeeNodeId =
@ManagerNodeId.GetDescendant(@LastManagerChild, NULL)
-- Add the row
INSERT INTO Employee
(NodeId, EmployeeId, EmployeeName, Title)
VALUES
(@NewEmployeeNodeId, @EmployeeId, @EmployeeName, @Title)
COMMIT
END
GO
You
can now use this stored procedure to easily insert new rows into the
employee table as a child of any node within the hierarchy. The stored
procedure accepts a @ManagerId
parameter that the caller provides as the employee ID of the parent
that the newly added employee should be a child of. The remaining
parameters (@EmployeeId, @EmployeeName, and @Title) simply provide values for the new employee.
After declaring a few local hierarchyid variables, the procedure retrieves the hierarchyid of the parent specified by the @ManagerId input parameter, just as you did earlier for Amy, and stores the result in the local @ManagerNodeId variable, as shown here:
-- Get the hierarchyid of the desired parent passed in to @ManagerId
SELECT @ManagerNodeId = NodeId
FROM Employee
WHERE EmployeeId = @ManagerId
This is the value on which you now want to call GetDescendant in order to establish an appropriate hierarchyid
value for the new employee. You don’t particularly care about the order
of siblings beneath each parent, and so you can let the procedure
simply append the new employee to the end of any siblings that might
already exist. However, you still must provide the two positional
parameters expected by GetDescendant
that explicitly specify the point of insertion. Because you want new
siblings to be appended to the end, you know that the second positional
parameter should always be NULL. Your job therefore is to establish an appropriate value for the first positional parameter by ascertaining the hierarchyid value of the last existing sibling, or using NULL if there are no siblings. You achieve this with the following SELECT statement:
-- Get the hierarchyid of the last existing child beneath the parent
SELECT @LastManagerChild = MAX(NodeId)
FROM Employee
WHERE NodeId.GetAncestor(1) = @ManagerNodeId
This code introduces a new hierarchyid method, GetAncestor.
