THE SQL Server Blog Spot on the Web

Welcome to SQLblog.com - The SQL Server blog spot on the web Sign in | |
in Search

Peter DeBetta's SQL Programming Blog

Peter DeBetta's blog about programming in SQL Server using technologies such as T-SQL, .NET, CLR, C#, VB, Visual Studio, and SQL Server Management Studio.

2008: Converting XML to HIERARCHYID

While working on a new book, I have been experimenting with the new HIERARCHYID data type. And since I've seen comparisons drawn between using XML versus using HIERARCHYID to manage a hierarchy of data, I decided that it would likely be very useful if I could convert an XML structure into an equivalent relational structure using HIERARCHYID. Here is what I came up with:

DECLARE @x XML =
'<A id="1">
    <B id="2">
      <C id="3"/>
      <D id="4"/>
    </B> 
    <E id="5"/>
    <F id="6"/>
</A>';

WITH Folders (ID, ParentID, Description, RowNum) AS
(
    SELECT
        t.c.value('@id', 'int')
        , NULLIF(t.c.value('../@id', 'nvarchar(50)'), '')
        , t.c.value('local-name(.)', 'nvarchar(50)')
        , t.c.value('for $s in . return count(../*[. << $s]) + 1', 'int')
    FROM @x.nodes('//*') AS t(c)
)
, FolderTree AS
(
    SELECT ID, ParentID, Description, RowNum,
        HIERARCHYID::GetRoot() AS FolderNode
    FROM Folders
    WHERE ParentID IS NULL

    UNION ALL

    SELECT
F.ID, F.ParentID, F.Description, F.RowNum,
        CAST(FT.FolderNode.ToString() + CAST(F.RowNum AS varchar(50)) + '/'
            AS HIERARCHYID)
    FROM Folders AS F
        INNER JOIN FolderTree AS FT ON F.ParentID = FT.ID 
)
SELECT
    ID,
    ParentID,
    Description,
    FolderNode,
    FolderNode.ToString() AS Path
FROM FolderTree
ORDER BY FolderNode;

Which results in:

ID 

ParentID 

Description 

FolderNode 

Path 

NULL 

0x 

0x58 

/1/ 

0x5AC0

/1/1/ 

0x5B40 

/1/2/ 

0x68 

/2/ 

0x78 

/3/ 

The basis of this is the same as if converting an existing adjacency model to use HIERARCHYID – use a combination of a recursive CTE and row numbers partitioned by parent to generate the new path of the node and then cast that path to the HIERARCHYID data type. The parent path is retrieved using the ToString method of the parent node, and this is concatenated with the RowNum value and a forward slash (/) to result in the path for the current node. And finally, the newly generated path is then cast to HIERARCHYID, which uses its static Parse method behind the scenes to create a new instance of HIERARCHYID. This table shows the additional detail and should reveal how the new path is created.

ID 

ParentID 

Description 

FolderNode 

Path 

Parent Path 

RowNum

NULL 

0x 

N/A 

1

0x58 

/1/ 

/

1

0x5AC0

/1/1/ 

/1/

1

0x5B40 

/1/2/ 

/1/

2

0x68 

/2/ 

/

2

0x78 

/3/ 

/

3

On the XML side of things, I generated a partitioned row number by using the nodes method to return all nodes in the XML structure, as shown here: FROM @x.nodes('//*') AS t(c). The double forward slash (//) translates to "relative path" and the asterisk means any node, so this returns all nodes at all levels. Then, to return the row number, I use one of my favorite XQuery examples:

t.c.value('for $s in . return count(../*[. << $s]) + 1', 'int') AS RowNum

This translates to "count the number of immediate child nodes of my parent node that are positioned before me." Now that I have all the nodes, and a partitioned row number for these nodes, I can construct the new path of the node.

I will post more as I continue writing about and exploring SQL Server 2008.

Enjoy!

Published Sunday, December 09, 2007 1:39 PM by Peter W. DeBetta

Comment Notification

If you would like to receive an email when updates are made to this post, please register here

Subscribe to this post's comments using RSS

Comments

 

Eddie van halen said:

cool

December 13, 2007 11:25 PM
 

Zdenek said:

Hi

I have completly different problem. I need to convert hierarchyid to xml to be able to bind it to treeview control.

Any idea?

April 3, 2008 6:25 AM

Leave a Comment

(required) 
(required) 
Submit

About Peter W. DeBetta

Peter DeBetta works for Microsoft as an Application Platform Technology Strategist, specializing in strategies, design, implementation, and deployment of Microsoft SQL Server and .NET solutions. Peter writes courseware, articles, and books – most recently the title Introducing SQL Server 2008 from Microsoft Press. Peter speaks at conferences around the world, including TechEd, SQL PASS Community Summit, DevTeach, SQL Connections, DevWeek, and VSLive!

When Peter isn’t working, you can find him singing and playing guitar (click here to hear an original song by Peter), taking pictures, or simply enjoying life with his wife, son, and daughter.
Powered by Community Server (Commercial Edition), by Telligent Systems
  Privacy Statement