Convert 0 to NULL

NULLIF(Bu.StateId, 0)

Get all the child for the selected item : HierarchyId

 Declare @mgr HierarchyId
 select @mgr=HierarchyID from Hierarchies WHERE HierarchyID=hierarchyid::GetRoot()
Select H1.*,(Select CASE WHEN count(1)>1 THEN 1 ELSE 0 END FROM Hierarchies AS H2 WHERE H2.HierarchyID.IsDescendantOf(H1.HierarchyID)=1) AS IsChild from Hierarchies AS H1 WHERE HierarchyID.IsDescendantOf(@mgr)=1

Get next child of selected item : HierarchyId

SELECT HierarchyID.GetLevel(),H1.*,(Select CASE WHEN count(1)>1 THEN 1 ELSE 0 END FROM Hierarchies AS H2 WHERE H2.HierarchyID.IsDescendantOf(H1.HierarchyID)=1) AS IsChild
from Hierarchies AS H1
WHERE HierarchyID.GetAncestor(1) = @parentHierarchyId

HIERARCHYID::GetDescendant()

http://beyondrelational.com/modules/2/blogs/28/posts/18339/sql-server-2008-katmai-tsql-enhancements-part-9-hierarchyidgetdescendant.aspx

Create multiple root node using HierarchyId column

CREATE TABLE [Entity](
    [ID] [int] IDENTITY(1,1) NOT NULL,
    [Name] [varchar](50) NOT NULL,
    [Hierarchy] [hierarchyid] NOT NULL
 CONSTRAINT [PK_Entity] PRIMARY KEY CLUSTERED
(
    [ID] ASC
)
) ON [PRIMARY]

INSERT INTO [Entity]
      ([Name]
      ,[Hierarchy])
     VALUES
      ('Root A'
      ,hierarchyid::GetRoot().GetDescendant(NULL,NULL))


-- Create the second 'root'
INSERT INTO [Entity]
      ([Name]
      ,[Hierarchy])
     VALUES
      ('Root B'
      ,hierarchyid::GetRoot().GetDescendant((select MAX(hierarchy) from entity where hierarchy.GetAncestor(1) = hierarchyid::GetRoot()),NULL))