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: