How to Traverse Hierarchical XML Data With T-SQL in SQL Server 2005/2008 by Using Common Table Expressions
Posted on 6/1/2008 10:21:46 PM in #T-SQL

When you have to save hierarchical data inside a database you can traverse the tree with recursion and for each note call the database to save it. But that is not the most elegant solution; because your tree might be quite big and you can end up with lots of calls to the database. You may also need to wrap this whole thing inside a transaction but as we all know stretching a transaction between several calls is not the best approach.

What we actually want is to transfer the hierarchical tree inside a single stored procedure and process it there. But how can you pass a hierarchical data to a single stored procedure? Well the answer as often is XML. Let’s assume you have this hierarchical XML:

DECLARE @xml XML

SET @xml = '
<Root>
    <Folder Name="/">
        <Folder Name="One">
            <Folder Name="One_One" />
        </Folder>
        <Folder Name="Two">
            <Folder Name="Two_One">
                <Folder Name="Two_One_One">
                    <Folder Name="Two_One_One_One" />
                </Folder>
                <Folder Name="Two_One_Two" />
            </Folder>
        </Folder>
    </Folder>
</Root>';

Then in order to traverse it we have to combine common table expressions with CROSS APPLY call to the current child node. This may look as follow:

WITH CTE_ITEMS (Folders, [Name], [Path], Level)
AS
(
    SELECT 
        VIRT.node.query('./*') as [Folders],
        VIRT.node.value('@Name', 'nvarchar(500)') as [Name],
        CAST('/' as nvarchar(max)) as [Path],
        0 as [Level]
        FROM @xml.nodes('/Root/Folder') as VIRT(node)
    UNION ALL
    SELECT 
        VIRT.node.query('./*') as [Folders], 
        VIRT.node.value('@Name', 'nvarchar(500)') as [Name],
        [Path] + 
            CASE [Path] WHEN '/' THEN '' ELSE + '/' END + 
            VIRT.node.value('@Name', 'nvarchar(max)') as [Path],
        Level + 1 as [Level]
        FROM 
    CTE_ITEMS CROSS APPLY Folders.nodes('./Folder') as VIRT(node)
)
SELECT [Name],[Path],[Level] FROM CTE_ITEMS ORDER BY [Path]

And finally the result gives us all we need to traverse the tree:

Name Path Level
/ / 0
One /One 1
One_One /One/One_One 2
Two /Two 1
Two_One /Two/Two_One 2
Two_One_One /Two/Two_One/Two_One_One 3
Two_One_One_One /Two/Two_One/Two_One_One/Two_One_One_One 4
Two_One_Two /Two/Two_One/Two_One_Two 3

Share this post:   digg     Stumble Upon     del.icio.us     E-mail

enjio
Posted on 6/6/2008 1:57:09 PM

Is there a way to see the Parent Name in the list?

Vladimir Bodurov
Posted on 6/6/2008 2:05:45 PM

with the current query it won't be accessible with XPath because the recursive call does not include the parent node but you can get it from the path as: 

WITH CTE_ITEMS ([Parent], Folders, [Name], [Path], Level)   
AS   
(   
    SELECT    
        CAST(NULL as nvarchar(max)) as [ParentName],   
        VIRT.node.query('./*') as [Folders],   
        VIRT.node.value('@Name', 'nvarchar(500)') as [Name],   
        CAST('/' as nvarchar(max)) as [Path],   
        0 as [Level]   
        FROM @xml.nodes('/Root/Folder') as VIRT(node)   
    UNION ALL   
    SELECT   
        REVERSE(SUBSTRING( REVERSE([Path]) ,0 , CHARINDEX( '/', REVERSE([Path])) + 1 )) as [ParentName],    
        VIRT.node.query('./*') as [Folders],    
        VIRT.node.value('@Name', 'nvarchar(500)') as [Name],   
        [Path] +    
            CASE [Path] WHEN '/' THEN '' ELSE + '/' END +    
            VIRT.node.value('@Name', 'nvarchar(max)') as [Path],   
        Level + 1 as [Level]   
        FROM    
    CTE_ITEMS CROSS APPLY Folders.nodes('./Folder') as VIRT(node)   
)   
SELECT [Parent],[Name],[Path],[Level] FROM CTE_ITEMS ORDER BY [Path]
Kiran
Posted on 11/28/2008 12:08:36 AM

It was really a nice one. But, here in my case I need an xml to be generated from a table which had parent/child relation at multiple levels. That means exactly opposite to the example you have given.will it be possible to do in T-SQL.

Karsten Strobel
Posted on 3/23/2009 2:54:28 PM

Hi. Here's my approach... 

WITH CTE_ITEMS ([HId], [Folders], [Name], [Path]) 
AS 
( 
    SELECT  
        hierarchyid::GetRoot() as [HId], 
        VIRT.node.query('./*') as [Folders], 
        VIRT.node.value('@Name', 'nvarchar(500)') as [Name], 
        CAST('/' as nvarchar(max)) as [Path] 
        FROM @xml.nodes('/Root/*') as VIRT(node) 
    UNION ALL 
    SELECT  
        hierarchyid::Parse([HId].ToString() + VIRT.node.value('1+count(for $a in . return $a/../*[. << $a])', 'varchar(10)') + '/'), 
        VIRT.node.query('./*'),  
        VIRT.node.value('@Name', 'nvarchar(500)'), 
        [Path] +  
            CASE [Path] WHEN '/' THEN '' ELSE + '/' END +  
            VIRT.node.value('@Name', 'nvarchar(max)') 
    FROM  
    CTE_ITEMS CROSS APPLY Folders.nodes('./*') as VIRT(node) 
) 
SELECT [HId].ToString(),[Name],[Path],[HId].GetLevel() FROM CTE_ITEMS 
ORDER BY [HId]
elhaix
Posted on 12/11/2009 12:11:38 AM

This is so elegant!

Given: 

	SET @TestData = 
'<Receipt xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xmlns:xsd="http://www.w3.org/2001/XMLSchema" ReceiptID="0" UserID="0" UserCardID="0" PaymentMethodID="1" MerchantID="0" MerchantTaxID="MERCHANT_TAX_ID" MerchantReceiptID="MERCHANT_RECEIPT_ID" MerchantReceiptReferenceID="MERCHANT_RECEIPT_REF_ID" ReceiptTypeID="0" TransactionTypeID="2" MerchantReceiptDate="2009-12-10T18:01:14.2101141-07:00" Tax1PerCent="0" Tax2PerCent="0" Tax3PerCent="0" Tax1Total="0" Tax2Total="0" Tax3Total="0" TotalTax="5" Subtotal="100" ReceiptTotal="105" DateAdded="2009-12-10T18:01:14.2101141-07:00" MerchantStore="MERCHANT_STORE_NAME" StoreAddress1="228127 Long Street" StoreAddress2="" StoreCity="Los Angeles" StoreState="California" StoreZip="90212" StoreCountry="USA" StorePhone1="310-444-3333" StorePhone2="" StoreFax="310-333-2222" ReceiptHeader1="Test Receipt Header 1" ReceiptHeader2="Header 2" ReceiptHeader3="Header 3" ReceiptFooter1="Test Receipt Footer 1" ReceiptFooter2="Footer 2" ReceiptFooter3="Footer 3" ReceiptCreditCost="1" UserPaidForReceipt="false"> 
  <Errors /> 
  <ReceiptItem LineItemID="0" UserID="0" ReceiptID="0" MerchantItemID="111xxxTEST_ITEM_1" LineItemTypeID="1" ItemDesc1="Item 1 - Desc1: This is a test item purchased on a test receipt, line 1" ItemDesc2="Item 1 - Desc2: Item description, line 2" ItemDesc3="Item 1 - Desc3: Item description, line 3" Quantity="1" PricePerItem="50" LineItemTotal="50" DateAdded="2009-12-10T18:01:14.2101141-07:00"> 
    <Errors /> 
    <LineItemType LineItemTypeID="1" LineItemType="Purchase"> 
      <Errors /> 
    </LineItemType> 
  </ReceiptItem> 
. 
. 
.

I was surprised at how simple it was there-after. Didn't really even have to go through specifying paths! 

SET @UserID = 9 
SET @ReceiptID = 111222 
 
;WITH CTE_ITEMS (Receipt, MerchantItemID, LineItemTypeID, 
				ItemDesc1, ItemDesc2, ItemDec3, ItemNumber, Quantity, 
				PricePerItem, LineItemTotal, DateAdded) 
AS 
( 
	-- we're only interested in the receipt line items 
	-- AS we've already retrieved the receipt top-level items 
    SELECT  
        VIRT.node.query('./*') AS Receipt, 
        --@UserID AS UserID, 
        --@ReceiptID AS REceipt, 
        VIRT.node.value('@MerchantItemID', 'varchar(50)') AS MerchantItemID, 
        VIRT.node.value('@LineItemTypeID', 'int') AS LineItemTypeID, 
        VIRT.node.value('@ItemDesc1', 'varchar(128)') AS ItemDesc1, 
        VIRT.node.value('@ItemDesc2', 'varchar(128)') AS ItemDesc2, 
        VIRT.node.value('@ItemDesc3', 'varchar(128)') AS ItemDesc3, 
        VIRT.node.value('@ItemNumber', 'varchar(128)') AS ItemNumber, 
        VIRT.node.value('@Quantity', 'int') AS Quantity, 
        VIRT.node.value('@PricePerItem', 'decimal(18,2)') AS PricePerItem, 
        VIRT.node.value('@LineItemTotal', 'decimal(18,2)') AS LineItemTotal, 
        VIRT.node.value('@DateAdded', 'varchar(128)') AS DateAded 
    FROM @TestData.nodes('/Receipt/ReceiptItem') AS VIRT(node) 
     
    UNION ALL 
     
    SELECT  
        VIRT.node.query('./*') AS Receipt, 
        --@UserID AS UserID, 
        --@ReceiptID AS REceipt, 
        VIRT.node.value('@MerchantItemID', 'varchar(50)') AS MerchantItemID, 
        VIRT.node.value('@LineItemTypeID', 'int') AS LineItemTypeID, 
        VIRT.node.value('@ItemDesc1', 'varchar(128)') AS ItemDesc1, 
        VIRT.node.value('@ItemDesc2', 'varchar(128)') AS ItemDesc2, 
        VIRT.node.value('@ItemDesc3', 'varchar(128)') AS ItemDesc3, 
        VIRT.node.value('@ItemNumber', 'varchar(128)') AS ItemNumber, 
        VIRT.node.value('@Quantity', 'int') AS Quantity, 
        VIRT.node.value('@PricePerItem', 'decimal(18,2)') AS PricePerItem, 
        VIRT.node.value('@LineItemTotal', 'decimal(18,2)') AS LineItemTotal, 
        VIRT.node.value('@DateAdded', 'varchar(128)') AS DateAded 
    FROM CTE_ITEMS CROSS APPLY Receipt.nodes('./ReceiptItem') AS VIRT(node) 
) 
SELECT @UserID AS UserID, @ReceiptID AS ReceiptID, -- Receipt, 
		MerchantItemID, LineItemTypeID, 
				ItemDesc1, ItemDesc2, ItemDec3, ItemNumber, Quantity, 
				PricePerItem, LineItemTotal, DateAdded 
FROM CTE_ITEMS  
[/code 
 
I modified it to traverse my own parent/child XML tree - works like s charm! 
 
Thanks guys!
website designing
Posted on 1/16/2010 10:10:09 AM

Hy, This is a very informative and interedted site, gud work done by you guys. keep it up. thanks

Designer
Posted on 4/18/2010 7:37:42 AM

Not bad, i'll try to use this code at our project with SOAP-extension.
In our Project, it will be very useful thing!

Thanks

Andreas Moehlenbrock
Posted on 11/8/2010 9:41:57 PM

Thank you all for sharing this, I had similar problem as Karsten, need parentId, but have only SQL Server 2005 available, hierarchyid::GetRoot() works only for SQL Server 2008. So I enhanced the sample shown above as follows: 

DECLARE @xml XML 
 
SET @xml = ' 
<Root> 
    <Folder Id="1" Name="/"> 
        <Folder Id="2" Name="One"> 
            <Folder Id="3" Name="One_One" /> 
        </Folder> 
        <Folder Id="4" Name="Two"> 
            <Folder Id="5" Name="Two_One"> 
                <Folder Id="6" Name="Two_One_One"> 
                    <Folder Id="7" Name="Two_One_One_One" /> 
                </Folder> 
                <Folder Id="8" Name="Two_One_Two" /> 
            </Folder> 
        </Folder> 
    </Folder> 
</Root>'; 

WITH CTE_ITEMS ([Parent], [ParentId], Folders, [Id], [Name], [Path], [Level])    
AS    
(    
    SELECT     
        CAST(NULL as nvarchar(max)) as [Parent], 
		CAST(NULL as int) as [ParentId], 
        VIRT.node.query('./*') as [Folders],    
        VIRT.node.value('@Id', 'int') as [Id],    
        VIRT.node.value('@Name', 'nvarchar(500)') as [Name],    
        CAST('/Root/Folder[@Id=' as nvarchar(max)) + VIRT.node.value('@Id', 'varchar(50)') + ']' as [Path], 
        0 as [Level]    
        FROM @xml.nodes('/Root/Folder') as VIRT(node)    
    UNION ALL    
    SELECT    
		[Path] AS [Parent], 
		CAST(SUBSTRING([Path], LEN([Path]) - CHARINDEX( '=', REVERSE([Path])) + 2, LEN([Path]) - CHARINDEX( ']', REVERSE([Path])) - (LEN([Path]) - CHARINDEX( '=', REVERSE([Path])) + 2) + 1) AS int) AS ParentId, 
        VIRT.node.query('./*') as [Folders], 
        VIRT.node.value('@Id', 'int') as [Id],    
        VIRT.node.value('@Name', 'nvarchar(500)') as [Name],    
        [Path] +     
            CASE [Path] WHEN '/' THEN '' ELSE + '/' END +     
            'Folder[@Id=' + VIRT.node.value('@Id', 'varchar(50)') + ']' as [Path], 
        Level + 1 as [Level] 
        FROM     
    CTE_ITEMS CROSS APPLY Folders.nodes('./Folder') as VIRT(node)    
)    
SELECT [Parent],[ParentId],[Id],[Name],[Path],[Level] FROM CTE_ITEMS ORDER BY [Path]

Have fun.

Cloud Hosting
Posted on 4/19/2011 5:09:21 PM

I have been looking all over the web to figure out how to traverse hierarchical XML data With T-SQL in SQL Server 2005/2008. I didn't even think about using common table expressions. Your solution is brilliant and elegant. Thanks for all of the help.

Commenting temporarily disabled