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.

Shredding XML into HIERARCHYID Take 2

In a previous blog post, I had discussed a method of shredding XML to a table with HIERARCHYID, and realized that it had a dependency that I was not too keen about: The XML data required an “id” attribute in order to create the hierarchy. I had sorted out a way to inject a unique attribute ID into all the nodes (I’ll discuss this in a follow up post), but having to modify the original XML didn’t have much appeal. But, upon reading another post by my fellow blogger, Adam Machanic, I realized it could be done without this requirement. Using the technique that Adam presented, I can generate unique paths to be parsed into a HIERARCHYID column.


SET
@x = '<a someAttribute="1"><b><c>abc</c><c anotherAttribute="2">def</c></b><b><c>abc</c><c>def</c></b></a>'

DECLARE @T TABLE (NodeName VARCHAR(255), Attributes XML, NodeText VARCHAR(MAX), HierarchyNode HIERARCHYID)
;
WITH N (Node, NodeName, Attributes, NodeText, HierarchyPath
AS
SELECT
      
CAST(Expr.query('.') AS XML) -- Node
      
, CAST(Expr.value('local-name(.)', 'varchar(255)') AS VARCHAR(255)) -- NodeName
      
, CASE WHEN Expr.value('count(./@*)', 'INT') >
              
THEN Expr.query('<a>{for $a in ./@* return $a}</a>'
              
ELSE NULL END -- Attributes
      
, CAST(Expr.value('./text()[1]', 'varchar(max)') AS VARCHAR(MAX)) -- NodeText
      
, CAST('/' AS VARCHAR(1000)) -- HierarchyPath
  
FROM @x.nodes('/*[1]') AS Res(Expr
       
  
UNION ALL
   
  
SELECT  
      
Expr.query('.') -- Node
      
, CAST(Expr.value('local-name(.)', 'varchar(255)') AS VARCHAR(255)) -- NodeName
      
, CASE WHEN Expr.value('count(./@*)', 'INT') >
              
THEN Expr.query('<a>{for $a in ./@* return $a}</a>'
              
ELSE NULL END -- Attributes
      
, CAST(Expr.value('./text()[1]', 'varchar(max)') AS VARCHAR(MAX)) -- NodeText
      
, CAST(N.HierarchyPath 
             + CAST(DENSE_RANK() OVER (ORDER BY Expr) AS VARCHAR(1000)) 
             +
'/' AS VARCHAR(1000)) -- HierarchyPath
  
FROM N CROSS APPLY Node.nodes('*/*') AS Res(Expr)
)
INSERT INTO @T (NodeName, Attributes, NodeText, HierarchyNode)
SELECT NodeName, Attributes, NodeText, CAST(HierarchyPath AS HIERARCHYID)
FROM N
ORDER BY CAST(HierarchyPath AS HIERARCHYID)

SELECT * FROM @T 

For this example, I simple grab the node name, the node text, and the attributes (when they exist) as a simple XML value of the format:

<a [attribute1=”attribute value” [attribute2=”attribute value”]...] />

Of course, these values could also be shredded into the hierarchy. One way of doing this would be to add an additional column to the results that represents the type of entry in the hierarchy (node versus attribute). My challenge to you is to create that solution.

Have fun!

Published Tuesday, February 09, 2010 1:18 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

 

Peter DeBetta's SQL Programming Blog said:

Now that I've discussed converting XML into a set of HIERARCHYID values I thought I'd try to reverse

February 17, 2010 4:15 PM
 

Amirul Choudhury said:

Hello Peter

I was wandering if storing and retrieving the data is faster with Hierarchy ID or XMl when you deal with Organizational Hierarchy (Company, Barnaches, Individual person etc. Any idea? Which is a faster method?

Thanks

Amirul

October 7, 2011 4:08 PM
 

simply click the up coming webpage said:

Peter DeBetta's SQL Programming Blog : Shredding XML into HIERARCHYID Take 2

October 16, 2014 11:48 PM

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