1 out of 1 rated this helpful - Rate this topic

GetReparentedValue (Database Engine)

SQL Server 2008 R2

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


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


-- CLR syntax
SqlHierarchyId GetReparentedValue ( SqlHierarchyId oldRoot , SqlHierarchyId newRoot )

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)

Reference

hierarchyid (Transact-SQL)

Concepts

hierarchyid Data Type Method Reference

Tutorial: Using the hierarchyid Data Type

Using hierarchyid Data Types (Database Engine)

Did you find this helpful? Yes No

Not accurate

Not enough depth

Need more code examples

(1500 characters remaining)

Community Additions

ADD