SQL Server: How to extract parent and its child from the output and store in my table

Question

<?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>  

This is my XML whose value i need to store in db table as parent child data. my table would look like where i will store the parent child relation

Table will have 4 columns. ID INT Identity PK, ParentID INT, NodeText VARCHAR(50), RecOrder INT where i want to store above xml data with relation like ID and ParentID.

Mr @Stefan Hoffmann show me how to show data in parent child relation from xml but still i am not being able to extract parent child relation from his output and store in my table.

His code

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 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;  

His output attached here,

please someone tell me how could i store the above output in my table with parent & child relation in such a way later i can extract & show any parent and its child data from my table.

thanks

Answer 1

Check an iterative method:

drop table if exists #Table  
  
create table #Table   
(   
    ID int not null IDENTITY(1, 1) PRIMARY KEY,  
    ParentID int null FOREIGN KEY REFERENCES #Table(ID),  
    NodeText varchar(50) not null,  
    RecOrder int 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>'  
  
---  
  
declare @c int =  @xml.value('count(//node)', 'int')  
declare @i int = 0  
  
while @i < @c  
begin  
    set @i += 1  
  
    insert into #Table(NodeText, RecOrder)  
    select t.n.value('@text', 'varchar(50)'), 1  
    from @xml.nodes('(//node)[sql:variable("@i")]') t(n)  
  
    declare @id int = SCOPE_IDENTITY()  
      
    set @xml.modify( 'insert attribute ID {sql:variable("@id")} into (//node)[sql:variable("@i")][1]')  
end  
  
;  
with Q as  
(  
    select   
        t.n.value('@ID', 'int') as id,  
        p.n.value('@ID', 'int') as parent_id  
    from @xml.nodes('//node') t(n)  
    cross apply t.n.nodes('..') p(n)  
)  
update t  
set ParentID = Q.parent_id  
from #Table as t  
left join Q on Q.id = t.id  
  
;  
with Q as  
(  
    select ID, ROW_NUMBER() over (partition by ParentID order by ID) as rn  
    from #Table  
)  
update t  
set RecOrder = Q.rn  
from #Table as t  
inner join Q on Q.id = t.id  
  
select * from #Table order by ID  

The guessed output is:

ID ParentID NodeText RecOrder
1 NULL Asia 1
2 1 China 1
3 2 Beijing 1
4 1 Pakistan 2
5 1 India 3
6 1 Sri Lanka 4
7 NULL Europe 2
8 7 Germany 1
9 NULL America 3
10 NULL Africa 4