GetReparentedValue (Database Engine)

Applies to: SQL Server Azure SQL Database Azure SQL Managed Instance

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

Syntax

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

Note

To view Transact-SQL syntax for SQL Server 2014 (12.x) and earlier versions, see Previous versions documentation.

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. Replace the oldRoot section of the current node to move the node.

Return Types

SQL Server return type:hierarchyid

CLR return type:SqlHierarchyId

Remarks

Used to modify the tree by moving nodes from oldRoot to newRoot. GetReparentedValue is used to move a hierarchy node to a new location in the hierarchy. The hierarchyid data type represents but doesn't 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 Hierarchical Data (SQL Server).

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 you move the node 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)  

See also

hierarchyid Data Type Method Reference
Hierarchical Data (SQL Server)
hierarchyid (Transact-SQL)