SQL Server 2019

By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.

-- Sample data.
--   Note that is has been modified to have more than 10 nodes with the same parent.
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="Albania"/> 
      <node text="Belarus"/> 
      <node text="Croatia"/> 
      <node text="Denmark"/> 
      <node text="Estonia"/> 
      <node text="Finland"/> 
      <node text="Georgia"/> 
      <node text="Hungary"/> 
      <node text="Iceland"/> 
      <node text="Kazakhstan"/> 
      <node text="Latvia"/> 
      <node text="Malta"/> 
      <node text="Netherlands"/> 
      <node text="Poland"/> 
      <node text="Romania"/> 
      <node text="San Marino"/> 
      <node text="Turkey"/> 
      <node text="Ukraine"/> 
      <node text="Vatican City"/> 
      <node text="Germany"/> 
    </node>
    <node text="America"/> 
    <node text="Africa"/> 
  </TreeView>';

-- Demonstrate how to query the XML to build a hierarchy of rows.
with Tree as (
  -- Process the root level nodes.
  select
    -- Assemble the node path using four-digit values to avoid confusion if a single node has many children.
    --   Note that the numbers assigned are based on the positions of the nodes in the XML data, thus the order is preserved.
    Cast( Right( '000' + Node.value( 'let $currentNode := . return count(../node[. << $currentNode])', 'VarChar(4)' ), 4 ) as VarChar(1024) ) as NodePath,
    Cast( NULL as VarChar(1024) ) as ParentNodePath, -- Root level nodes have no parent.
    Node.value( './@text', 'NVarChar(255)' ) as NodeText,
    Node.query( './*' ) as Children,
    0 as Depth -- Root level.
    from @xml.nodes( '/TreeView/node' ) as TreeView( Node )
  union all
  -- Add the children, one level at a time.
  select
    Cast( Coalesce( NodePath + '>', '' ) +
      Right( '000' + ChildNodes.Node.value( 'let $currentNode := . return count(../node[. << $currentNode])', 'VarChar(4)' ), 4 ) as VarChar(1024) ),
    NodePath,
    ChildNodes.Node.value( './@text', 'NVarChar(255)' ),
    ChildNodes.Node.query( './*' ),
    Depth + 1
    from Tree cross apply
      Children.nodes( '/node' ) as ChildNodes( Node )
  )
  select NodePath, ParentNodePath, Depth,
    Space( 2 * Depth ) + NodeText as IndentedNodeText
    from Tree
    order by NodePath;

-- Create the mysterious Table With Four Columns and a table to hold fixup data.
--   Note that this table cannot represent the order of nodes in the XML, only the parent/child relationships.
declare @MyTableWithFourColumns as Table ( Id Int Identity, ParentId Int, NodeText VarChar(50) );
declare @Fixups as Table ( MTWFCId Int, NodePath VarChar(1024), ParentNodePath VarChar(1024) );

-- Save the hierarchy in The Table.
with Tree as (
  -- Process the root level nodes.
  select
    -- Assemble the node path using four-digit values to avoid confusion if a single node has many children.
    --   Note that the numbers assigned are based on the positions of the nodes in the XML data, thus the order is preserved.
    Cast( Right( '000' + Node.value( 'let $currentNode := . return count(../node[. << $currentNode])', 'VarChar(4)' ), 4 ) as VarChar(1024) ) as NodePath,
    Cast( NULL as VarChar(1024) ) as ParentNodePath, -- Root level nodes have no parent.
    Node.value( './@text', 'NVarChar(255)' ) as NodeText,
    Node.query( './*' ) as Children
    from @xml.nodes( '/TreeView/node' ) as TreeView( Node )
  union all
  -- Add the children, one level at a time.
  select
    Cast( Coalesce( NodePath + '>', '' ) + Right( '000' + ChildNodes.Node.value( 'let $currentNode := . return count(../node[. << $currentNode])', 'VarChar(4)' ), 4 ) as VarChar(1024) ),
    NodePath,
    ChildNodes.Node.value( './@text', 'NVarChar(255)' ),
    ChildNodes.Node.query( './*' )
    from Tree cross apply
      Children.nodes( '/node' ) as ChildNodes( Node )
  )
  merge into @MyTableWithFourColumns
    using Tree as Source
    on 0 = 1 -- Since this is unlikely to happen the   when not matched   clause will insert all rows.
    when not matched then
      insert ( NodeText ) values ( NodeText )
      -- Merge lets us   output   columns that weren't inserted, something   insert   can't do.
      --   We'll keep the newly assigned   identity   value and the node and parent node paths.
      output Inserted.Id, Source.NodePath,Source.ParentNodePath into
        @Fixups( MTWFCId, NodePath, ParentNodePath );

-- The rows have been inserted, but the   ParentId   values have not been set.
select * from @MyTableWithFourColumns;
-- This data will let us fix up all of the missing parents.
select * from @Fixups;

-- Add the parents by cross referencing the parent/child relationships in   @Fixups .
update MTWFC
  set ParentId = Parent.MTWFCId
  from @MyTableWithFourColumns as MTWFC inner join
    @Fixups as Child on Child.MTWFCId = MTWFC.Id inner join
    @Fixups as Parent on Parent.NodePath = Child.ParentNodePath
  where MTWFC.ParentId is NULL and Child.ParentNodePath is not NULL;

-- See the results.
with Tree as (
  select Id, ParentId, NodeText, 0 as Depth, Cast( NodeText as VarChar(100) ) as IndentedNodeText,
    Cast( NodeText as VarChar(1024) ) as NodePath
    from @MyTableWithFourColumns
    where ParentId is NULL
  union all
  select MTWFC.Id, MTWFC.ParentId, MTWFC.NodeText, T.Depth + 1, Cast( Space( 2 * ( T.Depth + 1 ) ) + MTWFC.NodeText as VarChar(100) ) as IndentedNodeText,
    Cast( T.NodePath + ' > ' + MTWFC.NodeText as VarChar(1024) ) as NodePath
    from Tree as T inner join
      @MyTableWithFourColumns as MTWFC on MTWFC.ParentId = T.Id )
  select *
    from Tree
    order by NodePath;

NodePath	ParentNodePath	Depth	IndentedNodeText
0000	null	0	Asia
0000>0000	0000	1	China
0000>0000>0000	0000>0000	2	Beijing
0000>0001	0000	1	Pakistan
0000>0002	0000	1	India
0000>0003	0000	1	Sri Lanka
0001	null	0	Europe
0001>0000	0001	1	Albania
0001>0001	0001	1	Belarus
0001>0002	0001	1	Croatia
0001>0003	0001	1	Denmark
0001>0004	0001	1	Estonia
0001>0005	0001	1	Finland
0001>0006	0001	1	Georgia
0001>0007	0001	1	Hungary
0001>0008	0001	1	Iceland
0001>0009	0001	1	Kazakhstan
0001>0010	0001	1	Latvia
0001>0011	0001	1	Malta
0001>0012	0001	1	Netherlands
0001>0013	0001	1	Poland
0001>0014	0001	1	Romania
0001>0015	0001	1	San Marino
0001>0016	0001	1	Turkey
0001>0017	0001	1	Ukraine
0001>0018	0001	1	Vatican City
0001>0019	0001	1	Germany
0002	null	0	America
0003	null	0	Africa

Id	ParentId	NodeText
1	null	Asia
2	null	Europe
3	null	America
4	null	Africa
5	null	Albania
6	null	Belarus
7	null	Croatia
8	null	Denmark
9	null	Estonia
10	null	Finland
11	null	Georgia
12	null	Hungary
13	null	Iceland
14	null	Kazakhstan
15	null	Latvia
16	null	Malta
17	null	Netherlands
18	null	Poland
19	null	Romania
20	null	San Marino
21	null	Turkey
22	null	Ukraine
23	null	Vatican City
24	null	Germany
25	null	China
26	null	Pakistan
27	null	India
28	null	Sri Lanka
29	null	Beijing

MTWFCId	NodePath	ParentNodePath
1	0000	null
2	0001	null
3	0002	null
4	0003	null
5	0001>0000	0001
6	0001>0001	0001
7	0001>0002	0001
8	0001>0003	0001
9	0001>0004	0001
10	0001>0005	0001
11	0001>0006	0001
12	0001>0007	0001
13	0001>0008	0001
14	0001>0009	0001
15	0001>0010	0001
16	0001>0011	0001
17	0001>0012	0001
18	0001>0013	0001
19	0001>0014	0001
20	0001>0015	0001
21	0001>0016	0001
22	0001>0017	0001
23	0001>0018	0001
24	0001>0019	0001
25	0000>0000	0000
26	0000>0001	0000
27	0000>0002	0000
28	0000>0003	0000
29	0000>0000>0000	0000>0000

Id	ParentId	NodeText	Depth	IndentedNodeText	NodePath
4	null	Africa	0	Africa	Africa
3	null	America	0	America	America
1	null	Asia	0	Asia	Asia
25	1	China	1	China	Asia > China
29	25	Beijing	2	Beijing	Asia > China > Beijing
27	1	India	1	India	Asia > India
26	1	Pakistan	1	Pakistan	Asia > Pakistan
28	1	Sri Lanka	1	Sri Lanka	Asia > Sri Lanka
2	null	Europe	0	Europe	Europe
5	2	Albania	1	Albania	Europe > Albania
6	2	Belarus	1	Belarus	Europe > Belarus
7	2	Croatia	1	Croatia	Europe > Croatia
8	2	Denmark	1	Denmark	Europe > Denmark
9	2	Estonia	1	Estonia	Europe > Estonia
10	2	Finland	1	Finland	Europe > Finland
11	2	Georgia	1	Georgia	Europe > Georgia
24	2	Germany	1	Germany	Europe > Germany
12	2	Hungary	1	Hungary	Europe > Hungary
13	2	Iceland	1	Iceland	Europe > Iceland
14	2	Kazakhstan	1	Kazakhstan	Europe > Kazakhstan
15	2	Latvia	1	Latvia	Europe > Latvia
16	2	Malta	1	Malta	Europe > Malta
17	2	Netherlands	1	Netherlands	Europe > Netherlands
18	2	Poland	1	Poland	Europe > Poland
19	2	Romania	1	Romania	Europe > Romania
20	2	San Marino	1	San Marino	Europe > San Marino
21	2	Turkey	1	Turkey	Europe > Turkey
22	2	Ukraine	1	Ukraine	Europe > Ukraine
23	2	Vatican City	1	Vatican City	Europe > Vatican City