ÿþ---------------------1. Creating Table With Hierarchyid DataType----------------------------------------------------------------------------------



Create Table Mujudats

(

node hierarchyid PRIMARY KEY CLUSTERED,

level AS node.GetLevel() PERSISTED,

Mujud_id int Unique,

Mujud_name Nvarchar(30) Not null



)



-------------------2. Insert Root node into Mujudats ' table // Mujudat--------------------------------------------------------------------------- 





INSERT INTO [dbo].Mujudats VALUES (HierarchyID :: GetRoot(),5000,'Mujudat')



-------------------------3. Insert Right Child node into Mujudats ' table// Jandaran--------------------------------------------------------------

-- First we should declare some variables 



Declare @ManagerNode hierarchyid

Declare @Level hierarchyid



-----------------



Select @ManagerNode=node from Mujudats where mujud_id = 5000



---------GetDescendant will now return the first child "/1"

---------since there are no other children defined



Insert Into Mujudats values (@ManagerNode.GetDescendant(null,null),5001,'jundaran')





-------------------------4. Insert Left child node into Mujudats ' table //Bijanha---------------------------------------------------------------------



------now: if we are passing (jundaran,Null) to GetDesecndant() this 

------will give us the node value of next child after jundaran

------but if we are passing (Null,Jundaran), this function would return

------the node value of the child previous to Jundaran

------actually we insert a child before jundaran:







Declare @managerNode hierarchyid

Declare @Level hierarchyid



select @managerNode=node from Mujudats where Mujud_id=5000

Select @Level=node from Mujudats where Mujud_id =5001



insert into Mujudats Values(@managerNode.GetDescendant(@Level,null),5002,'Bijanha')





-------------------------------------Convert To string:

select node.ToString() AS NodeAsString,

node as NodeAsBinary,

node.GetLevel() AS Level,

Mujud_id,

Mujud_name

from Mujudats