I have posted for the some of the functions with examples and demonstrate them in earlier posts. I have written articles of HierarchyId data type overview, some basic functions, even demonstrate for the up-line & down-line of hierarchy nodes which you can read from below links.
Here I will present how can we get the next available node to be planed with HierarchyID data type function. Let's first create hierarchy data structure with the script.
-- Create a database and table CREATE DATABASE HierarchyDB GO USE HierarchyDB GO IF ( Object_id('HierarchyTab') > 0 ) DROP TABLE HierarchyTab GO CREATE TABLE HierarchyTab ( NodeId INT IDENTITY(1, 1) ,NodeDepth VARCHAR(100) NOT NULL ,NodePath HIERARCHYID NOT NULL ,NodeDesc VARCHAR(100) ) GO -- Creating constraint on hierarchy data type. ALTER TABLE HierarchyTab ADD CONSTRAINT U_NodePath UNIQUE CLUSTERED (NodePath) GO -- Inserting data in above creatd table. INSERT INTO HierarchyTab(NodeDepth,NodePath,NodeDesc) VALUES ('1',HIERARCHYID::Parse('/'),'Node-1'), ('1.1',HIERARCHYID::Parse('/1/'),'Node-2'), ('1.1.1',HIERARCHYID::Parse('/1/1/'),'Node-3'), ('1.1.2',HIERARCHYID::Parse('/1/2/'),'Node-4'), ('1.2',HIERARCHYID::Parse('/2/'),'Node-5'), ('1.2.1',HIERARCHYID::Parse('/2/1/'),'Node-6'), ('1.2.2',HIERARCHYID::Parse('/2/2/'),'Node-7'), ('1.2.2.1',HIERARCHYID::Parse('/2/2/1/'),'Node-8'), ('1.2.2.1.1',HIERARCHYID::Parse('/2/2/1/1/'),'Node-9'), ('1.2.2.1.2',HIERARCHYID::Parse('/2/2/1/2/'),'Node-10'), ('1.3',HIERARCHYID::Parse('/3/'),'Node-11'), ('1.3.1',HIERARCHYID::Parse('/3/1/'),'Node-12'), ('1.3.2',HIERARCHYID::Parse('/3/2/'),'Node-13'), ('1.4',HIERARCHYID::Parse('/4/'),'Node-14') GOHierarchy data structure as imaged as below.
(Click on image to enlarge)
How to we find it?
GetDescendant() : This function will give next available node where we can place new node.
1. GetDescendant(NULL,NULL) : will return default next left node.
2. GetDescendant(LeftNode,NULL) : will return right node next to left node.
3. GetDescendant(NULL,RighNode) : will return left node previous to left node.
Run the following script and see the output.
-- GetDescendant() SELECT NodePath.GetLevel() AS NodeLevel, NodePath.ToString() AS NodeStringPath, NodePath.GetDescendant(NULL,NULL).ToString() AS NextDefaultNode, -- Above line will get default node. NodeId, NodeDepth, NodePath, NodeDesc FROM HierarchyTab GO
Looking for one hierarchy node id 13,
SELECT NodePath.GetLevel() AS NodeLevel, NodePath.ToString() AS NodeStringPath, NodePath.GetDescendant(NULL,NULL).ToString() AS NextDefaultNode, NodePath.GetDescendant(HIERARCHYID::Parse('/3/2/1/'),NULL).ToString() AS NextRightNode, NodePath.GetDescendant(NULL,HIERARCHYID::Parse('/3/2/2/')).ToString() AS NextLeftNode, NodeId, NodeDepth, NodePath, NodeDesc FROM HierarchyTab WHERE NodeId = 13 GO
(Click on image to enlarge)
This is what I want to share here and hope you like it.
Comments
Post a Comment