You are here:Home » tsql » Get Level and String Path with HierarchyId DataType – SQL Server 2008

Get Level and String Path with HierarchyId DataType – SQL Server 2008

In a previous post I have published of the overview of HierarchyId data type. In this post I am writing about the details and the methods with hierarchy with HierarchyId data type.
Before going ahead with this post, please read the previous post here. The example which I am going to demonstrate HierarchyId and the methods for the same will drive you in details. Let’s create database and table objects.
-- Create 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 
Now inserting some sample records of hierarchical data.
-- 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')

GO
Here is the logical image of the above data are as follows,


(Click on image to enlarge)

Now we will look for the some of the methods of HierarchyId data type. We will go through GetRoot, ToString, Parse and GetLevel methods here and demonstrate in details for each. Let's drive with me here. Before please my earlier post of overview of such methods here.

1. GetRoot()

-- GetRoot()
SELECT 
HIERARCHYID::GetRoot() AS RootNode
,HIERARCHYID::GetRoot().ToString() AS RootNodePath

GO
Output :
RootNode   RootnodePath
------------   -----------------

0x                /

2. ToString()
-- ToString()
SELECT
NodePath.ToString() AS NodeStringPath
,NodeId
,NodeDepth
,NodePath
,NodeDesc
FROM   HierarchyTab

GO 

3. Parse()
-- Parse()
SELECT
HIERARCHYID::Parse(NodePath.ToString()) AS NodeVarBinaryPath,
NodePath.ToString() AS NodeStringPath,
NodeId,
NodeDepth,
NodePath,
NodeDesc
FROM HierarchyTab

GO

4. GetLevel()
-- GetLevel()
SELECT 
NodePath.GetLevel() AS NodeLevel,
NodePath.ToString() AS NodeStringPath,
NodeId,
NodeDepth,
NodePath,
NodeDesc
FROM HierarchyTab

GO

Now you have a very good idea and understanding for HierarchyId data type after reading this post. You can also read the Hiercrchyd Datatype Overview.

I will write next future post for other methods of HierarchyId data type.