• Article

GetReparentedValue (Database Engine)

Returns a node whose path from the root is the path to newRoot, followed by the path from oldRoot to this.

Syntax

-- Transact-SQL syntax
node. GetReparentedValue (oldRoot, newRoot )

-- CLR syntax
SqlHierarchyId GetReparentedValue (SqlHierarchyIdoldRoot , SqlHierarchyIdnewRoot )

Arguments

  • oldRoot
    A hierarchyid that is the node that represents the level of the hierarchy that is to be modified.

  • newRoot
    A hierarchyid that represents the node that will replace the oldRoot section of the current node in order to move the node.

Return Types

**SQL Server return type:**hierarchyid

**CLR return type:**SqlHierarchyId

Remarks

Can be used to modify the tree by moving nodes from oldRoot to newRoot. GetReparentedValue can be used to move a node of a hierarchy to a new location in the hierarchy. The hierarchyid data type represents but does not enforce the hierarchical structure. Users must ensure that the hierarchyid is appropriately structured for the new location. A unique index on the hierarchyid data type can help prevent duplicate entries. For an example of moving an entire subtree, see Working with hierarchyid Data.

Examples

A. Comparing two node locations

The following example shows the current hierarchyid of a node. It also shows what the hierarchyid of the node would be if the node were moved to become a descendant of the @NewParent node. It uses the ToString() method to show the hierarchical relationships.

DECLARE @SubjectEmployee hierarchyid , @OldParent hierarchyid, @NewParent hierarchyid
SELECT @SubjectEmployee = OrgNode FROM HumanResources.EmployeeDemo
  WHERE LoginID = 'adventure-works\gail0' ;
SELECT @OldParent = OrgNode FROM HumanResources.EmployeeDemo
  WHERE LoginID = 'adventure-works\roberto0' ; -- who is /1/1/
SELECT @NewParent = OrgNode FROM HumanResources.EmployeeDemo
  WHERE LoginID = 'adventure-works\wanida0' ; -- who is /2/3/

SELECT OrgNode.ToString() AS Current_OrgNode_AS_Text, 
(@SubjectEmployee. GetReparentedValue(@OldParent, @NewParent) ).ToString() AS Proposed_OrgNode_AS_Text,
OrgNode AS Current_OrgNode,
@SubjectEmployee. GetReparentedValue(@OldParent, @NewParent) AS Proposed_OrgNode,
FROM HumanResources.EmployeeDemo
WHERE OrgNode = @SubjectEmployee ;
GO

B. Updating a node to a new location

The following example uses GetReparentedValue() in an UPDATE statement to move a node from an old location to a new location in the hierarchy:

DECLARE @SubjectEmployee hierarchyid , @OldParent hierarchyid, @NewParent hierarchyid
SELECT @SubjectEmployee = OrgNode FROM HumanResources.EmployeeDemo
  WHERE LoginID = 'adventure-works\gail0' ; -- Node /1/1/2/
SELECT @OldParent = OrgNode FROM HumanResources.EmployeeDemo
  WHERE LoginID = 'adventure-works\roberto0' ; -- Node /1/1/
SELECT @NewParent = OrgNode FROM HumanResources.EmployeeDemo
  WHERE LoginID = 'adventure-works\wanida0' ; -- Node /2/3/

UPDATE HumanResources.EmployeeDemo
SET OrgNode = @SubjectEmployee. GetReparentedValue(@OldParent, @NewParent) 
WHERE OrgNode = @SubjectEmployee ;

SELECT OrgNode.ToString() AS Current_OrgNode_AS_Text, 
FROM HumanResources.EmployeeDemo
WHERE LoginID = 'adventure-works\gail0' ; -- Now node /2/3/2/

C. CLR example

The following code snippet calls the GetReparentedValue () method:

this. GetReparentedValue(oldParent, newParent)