SQL Server: How to save treeview nested node collection in table with same hierarchy of data as per XML

Question

This is my XML whose value i need to store in db table.

<?xml version="1.0" encoding="us-ascii" ?>   
 <TreeView>  
    <node text="Asia">  
     <node text="China">  
       <node text="Beijing"/>  
     </node>  
     <node text="Pakistan"/>   
     <node text="India"/>   
     <node text="Srilanka"/>   
    </node>  
    <node text="Europe">  
       <node text="Germany"/>   
       </node>  
    <node text="America"/>   
    <node text="Africa"/>   
 </TreeView>  

@Viorel shared a code example which insert data into table from xml but the problem of that code is data hierarchy not maintain when it is stored in table. if you run the below code then understand haphazardly data stored in table.

run this code please

drop table if exists Tree  
      
 create table Tree   
 (   
     id int NOT NULL PRIMARY KEY,   
     parent_id int REFERENCES Tree(id),   
     [text] nvarchar(max) NOT NULL   
 )  
      
 declare @xml as xml = '<?xml version="1.0" encoding="us-ascii" ?>   
  <TreeView>  
     <node text="Asia">  
      <node text="China">  
        <node text="Beijing"/>  
      </node>  
      <node text="Pakistan"/>   
      <node text="India"/>   
      <node text="Sri Lanka"/>   
     </node>  
     <node text="Europe">  
       <node text="Germany"/>   
     </node>  
     <node text="America"/>   
     <node text="Africa"/>   
  </TreeView>  
 '  
      
      
 ;  
 with Q1 as  
 (  
     select   
         n.query('.') as x,   
         cast(NULL as nvarchar(max)) as txt,  
         cast(0x as varbinary(max)) as p   
     from @xml.nodes('/') n(n)  
     union all  
     select   
         n.query('.'),   
         v.txt,   
         p + cast(cast((ROW_NUMBER() over (order by v.txt)) as int) as varbinary(max))  
     from Q1  
     cross apply x.nodes('*/node') n(n)  
     cross apply (values (n.value('@text', 'nvarchar(max)'))) as v(txt)  
 ),  
 Q2 as  
 (  
     select txt, p, ROW_NUMBER() over(order by len(p), p) as id  
     from Q1  
     where p <> 0x  
 ),  
 Q3 as  
 (  
     select Q2.id, a.id as parent_id, Q2.txt  
     from Q2  
     left join Q2 as a on a.p = substring(Q2.p, 1, len(Q2.p)-4)  
 )  
 insert into Tree  
 select * from Q3  
      
      
 select * from Tree order by 1  

in xml there is Asia is first Node and China is second node and china has child node but when anyone see the output then must notice data order is not maintain when data saved in table.

so please guide me what to change in above code as a result the way data exist in xml the same order is also maintain when data will be stored in table?

in my case ID column is PK and also identity type. so how could i capture the ID after insert each record because that ID need to be stored as Parent ID of child records. in this code ID value is automatically generated which i do not want because ID column is identity type in my case.

please tell me what changes need to be done in above code to store data with same order as it is in XML.

Thanks

Answer 1

The problem is that you data does not contain an order criteria and sets in SQL Server only guarantee order when using an ORDER BY clause.

Due to implementation nodes() returns nodes in-order, so you can apply ROW_NUMBER(), e.g.

SELECT x.value('element[1]', 'INT') ,  
       ROW_NUMBER() OVER ( ORDER BY x ) AS RN  
FROM   xml.nodes('/') A(x);  

when run MAXDOP 1, then it should work. If MAXDOP > 1 or in edge cases this may fail, just because there is no guarantee for the order in the set processing.

The only way I'm aware of is to create that order in the XML using XML processing, where order is guaranteed. Either by preprocessing it using XSLT or using FLOWR.

E.g.

DECLARE @xml AS XML = '<?xml version="1.0" encoding="us-ascii" ?>   
<TreeView>  
 <node text="Asia">  
   <node text="China">  
     <node text="Beijing"/>  
   </node>  
   <node text="Pakistan"/>   
   <node text="India"/>   
   <node text="Sri Lanka"/>   
 </node>  
 <node text="Europe">  
   <node text="Germany"/>   
 </node>  
 <node text="America"/>   
 <node text="Africa"/>   
</TreeView>';  
  
SELECT Q.n.value('./@text', 'NVARCHAR(255)') ,  
       Q.n.value('let $currentNode := . return count(../node[. << $currentNode])', 'INT')  
FROM   @xml.nodes('/TreeView/node') Q(n)  
ORDER BY 1;  

And the final tree parsing is applying the above to a hierarchic CTE:

WITH Hierarchy  
AS ( SELECT A.n.value('./@text', 'NVARCHAR(255)') AS NodeText ,  
            A.n.query('.') AS CurrentNode ,  
            0 AS NodeLevel ,  
            '\\' + A.n.value('let $currentNode := . return count(../node[. << $currentNode])', 'VARCHAR(MAX)') AS NodePath  
     FROM   @xml.nodes('/TreeView/node') A(n)  
     UNION ALL  
     SELECT C.n.value('./@text', 'NVARCHAR(255)') AS NodeText ,  
            C.n.query('.') AS CurrentNode ,  
            P.NodeLevel + 1 ,  
            P.NodePath + '\' + C.n.value('let $currentNode := . return count(../node[. << $currentNode])', 'VARCHAR(MAX)') AS NodePath  
     FROM   Hierarchy P  
            CROSS APPLY P.CurrentNode.nodes('node/node') C(n) )  
SELECT   NodeText ,  
         REPLICATE(SPACE(4), NodeLevel) + NodeText ,  
         NodeLevel ,  
         NodePath  
FROM     Hierarchy  
ORDER BY NodePath;  

Answer 2

Hi @Sudip Bhatt ,

You can try the following method. It keeps the XML hierarchy, and provides XPath expressions to access any leaf node with a value.

This is one of the rare case when OPENXML is useful. Otherwise, XQuery rules the world.

SQL

DECLARE @xml XML =  
N'<TreeView>  
	<node text="Asia">  
		<node text="China">  
			<node text="Beijing"/>  
		</node>  
		<node text="Pakistan"/>  
		<node text="India"/>  
		<node text="Srilanka"/>  
	</node>  
	<node text="Europe">  
		<node text="Germany"/>  
	</node>  
	<node text="America"/>  
	<node text="Africa"/>  
</TreeView>';  
  
DECLARE @DocHandle INT;  
EXEC sp_xml_preparedocument @DocHandle OUTPUT, @xml;  
  
;WITH rs AS  
(  
   SELECT * FROM OPENXML(@DocHandle,'/*')  
), cte AS  
(  
   -- anchor  
    SELECT id  
      ,ParentID  
      , [text]  
      ,CAST(id AS VARCHAR(100)) AS [Path]  
      ,CAST('/' + rs.localname AS VARCHAR(1000))   
     + N'['   
        + CAST(ROW_NUMBER() OVER(ORDER BY (SELECT 1)) AS NVARCHAR)    
        + N']' AS [XPath]  
    FROM rs  
    WHERE ParentID IS NULL  
    UNION ALL  
    --recursive member  
    SELECT t.id  
      ,t.ParentID  
      , t.[text]  
      , CAST(a.[Path] + ',' + CAST( t.ID AS VARCHAR(100)) AS VARCHAR(100)) AS [Path]  
      , CAST(a.[XPath] + '/' + IIF(t.nodetype = 2, '@', '')  
         + t.localname AS VARCHAR(1000))   
       + N'['  
      + CAST(ROW_NUMBER() OVER(PARTITION BY t.localname ORDER BY (SELECT 1)) AS NVARCHAR)  
      + N']' AS [XPath]  
    FROM rs AS t  
      INNER JOIN cte AS a ON t.ParentId = a.id  
)  
SELECT ID, ParentID, /*nodetype,*/ [Path]  
   , REPLACE([XPath],'[1]/#text[1]','') AS XPath, [text] AS [Value]  
   , CONCAT(REPLICATE(N'--', LEN([Path]) - LEN(REPLACE([Path], ',', '')) - 3), [text]) AS [Indented]  
FROM cte  
WHERE [text] IS NOT NULL  
ORDER BY [XPath];  
  
EXEC sp_xml_removedocument @DocHandle;  

Output