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)