Parse Hierarchy(TreeView) XML using Outer Apply

/*

This sample will help you to parse Tree View XMl of Explicit format.
If you Apply Cross Apply on for Outer Apply then it will not display the child which does not have 3 Levl

If you store this XML in to temp table then you can update the Parent child clasification in to table.

*/
-- Please Replace [ and ] by < and >
DECLARE @HierarchyDetail XML

SET @HierarchyDetail =
'[treeview]


[treeview-nodes]

[treeview-node node-text="Account -1001" node-value="1001"]

[treeview-nodes]

[treeview-node node-text="Account -1075" node-value="1075"]

[treeview-nodes]

[treeview-node node-text="Account - 1132" node-value="1132" /]

[/treeview-nodes]

[/treeview-node]

[/treeview-nodes]

[/treeview-node]

[treeview-node node-text="Account - 1000" node-value="1000"]

[treeview-nodes]

[treeview-node node-text="Account -1077" node-value="1077"]

[treeview-nodes]

[treeview-node node-text="Account -1134" node-value="1134" /]

[/treeview-nodes]

[/treeview-node]

[/treeview-nodes]

[/treeview-node]

[treeview-node node-text="Account - 1080" node-value="1080"]

[treeview-nodes]

[treeview-node node-text="Account - 1101 " node-value="1101"]

[treeview-nodes]

[treeview-node node-text="Account - 1135" node-value="1135" /]

[/treeview-nodes]

[/treeview-node]

[/treeview-nodes]

[/treeview-node]

[treeview-node node-text="Account - 1114" node-value="1114"]

[treeview-nodes]

[treeview-node node-text="Account - 1124 " node-value="1124"]

[treeview-nodes]

[treeview-node node-text="Account - 1137" node-value="1137" /]

[treeview-node node-text="Account - 2937 " node-value="2937" /]

[/treeview-nodes]

[/treeview-node]

[/treeview-nodes]

[/treeview-node]

[/treeview-nodes]

[/treeview]'

 

SELECT Level1.value('@node-value','VARCHAR(200)') AS Level1ID

,Level1.value('@node-text','VARCHAR(200)') AS Level1AName

,Level2.value('@node-value','VARCHAR(200)') AS Level2ID

,Level2.value('@node-text','VARCHAR(200)') AS Level2Name

,Level3.value('@node-value','VARCHAR(200)') AS Level3ID

,Level3.value('@node-text','VARCHAR(200)') AS Level3Name

FROM @HierarchyDetail.nodes('/treeview/treeview-nodes/treeview-node') AS Accounts(Level1) -- I Level Nodes

OUTER APPLY Level1.nodes('treeview-nodes/treeview-node') AS Level2Items(Level2) -- II Level Nodes

OUTER APPLY Level2.nodes('treeview-nodes/treeview-node') AS Level3Items(Level3) -- III Level Nodes