Pages

Saturday, June 1, 2013

How to convert HierarchyID to string?

Execute the following Microsoft SQL Server T-SQL scripts in SSMS Query Editor to demonstrate the converting HierarchyID to strings and string to HierarchyID conversion.

-- Convert HierarchyID to string and reverse
SELECT TOP 1000 [BusinessEntityID]
      ,[NationalIDNumber]
      ,[OrganizationNode]
      ,[OrganizationNode].ToString() as AlphaHierarchyID
      ,[OrganizationLevel]
      ,hierarchyid::Parse([OrganizationNode].ToString()+'1/') AS AlphaToHierarchyID
  FROM [AdventureWorks2008].[HumanResources].[Employee]
  ORDER BY BusinessEntityID
/*  BusinessEntityID    NationalIDNumber  OrganizationNode  AlphaHierarchyID 
                        OrganizationLevel AlphaToHierarchyID
1     295847284   0x    /     0     0x58
2     245797967   0x58  /1/   1     0x5AC0
3     509647174   0x5AC0      /1/1/ 2     0x5AD6
4     112457891   0x5AD6      /1/1/1/     3     0x5AD6B0
5     695256908   0x5ADA      /1/1/2/     3     0x5ADAB0
6     998320692   0x5ADE      /1/1/3/     3     0x5ADEB0
7     134969118   0x5AE1      /1/1/4/     3     0x5AE158
......*/
------------

No comments:

Post a Comment