Sql Server: How To Extract Parent Child Relation From Xml & Store In Table

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 IN

Solution 1:

DBfiddle for those who can't wait.

Lets start with some extended sample data:

-- 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><nodetext="Asia"><nodetext="China"><nodetext="Beijing"/></node><nodetext="Pakistan"/><nodetext="India"/><nodetext="Sri Lanka"/></node><nodetext="Europe"><nodetext="Albania"/><nodetext="Belarus"/><nodetext="Croatia"/><nodetext="Denmark"/><nodetext="Estonia"/><nodetext="Finland"/><nodetext="Georgia"/><nodetext="Hungary"/><nodetext="Iceland"/><nodetext="Kazakhstan"/><nodetext="Latvia"/><nodetext="Malta"/><nodetext="Netherlands"/><nodetext="Poland"/><nodetext="Romania"/><nodetext="San Marino"/><nodetext="Turkey"/><nodetext="Ukraine"/><nodetext="Vatican City"/><nodetext="Germany"/></node><nodetext="America"/><nodetext="Africa"/></TreeView>';

The following query can extract the parent/child hierarchy from the XML data while maintaining the order of the nodes. It uses a Common Table Expression (CTE) to process the levels of the hierarchy recursively.

-- 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 ) asVarChar(1024) ) as NodePath,
    Cast( NULLasVarChar(1024) ) as ParentNodePath, -- Root level nodes have no parent.
    Node.value( './@text', 'NVarChar(255)' ) as NodeText,
    Node.query( './*' ) as Children,
    0as Depth -- Root level.from@xml.nodes( '/TreeView/node' ) as TreeView( Node )
  unionall-- Add the children, one level at a time.selectCast( Coalesce( NodePath +'>', '' ) +Right( '000'+ ChildNodes.Node.value( 'let $currentNode := . return count(../node[. << $currentNode])', 'VarChar(4)' ), 4 ) asVarChar(1024) ),
    NodePath,
    ChildNodes.Node.value( './@text', 'NVarChar(255)' ),
    ChildNodes.Node.query( './*' ),
    Depth +1from Tree cross apply
      Children.nodes( '/node' ) as ChildNodes( Node )
  )
  select NodePath, ParentNodePath, Depth,
    Space( 2* Depth ) + NodeText as IndentedNodeText
    from Tree
    orderby NodePath;

Now for the Answer to the Question. We can use a simplified version of the CTE to provide the data to a merge statement that will insert the NodeText into the target table. An output clause on a merge can provide values that were not inserted, something an insert statement can't do. That lets us save the identity values assigned to the new rows along with the parent/child relationships from the CTE in a table (@Fixups) that we will use in an update to correct the ParentId values.

-- 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@MyTableWithFourColumnsasTable ( Id IntIdentity, ParentId Int, NodeText VarChar(50) );
declare@FixupsasTable ( 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 ) asVarChar(1024) ) as NodePath,
    Cast( NULLasVarChar(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 )
  unionall-- Add the children, one level at a time.selectCast( Coalesce( NodePath +'>', '' ) +Right( '000'+ ChildNodes.Node.value( 'let $currentNode := . return count(../node[. << $currentNode])', 'VarChar(4)' ), 4 ) asVarChar(1024) ),
    NodePath,
    ChildNodes.Node.value( './@text', 'NVarChar(255)' ),
    ChildNodes.Node.query( './*' )
    from Tree cross apply
      Children.nodes( '/node' ) as ChildNodes( Node )
  )
  mergeinto@MyTableWithFourColumnsusing Tree as Source
    on0=1-- Since this is unlikely to happen the   when not matched   clause will insert all rows.whennot matched theninsert ( 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 .--   Note that this will only update the newly added rows. Any preexisting data will be unchanged.update MTWFC
  set ParentId = Parent.MTWFCId
  from@MyTableWithFourColumnsas MTWFC innerjoin@Fixupsas Child on Child.MTWFCId = MTWFC.Id innerjoin@Fixupsas Parent on Parent.NodePath = Child.ParentNodePath
  where MTWFC.ParentId isNULLand Child.ParentNodePath isnotNULL;

-- See the results:select*from@MyTableWithFourColumns;
-- Or, with nicer formatting:with Tree as (
  select Id, ParentId, NodeText, 0as Depth, Cast( NodeText asVarChar(100) ) as IndentedNodeText,
    Cast( NodeText asVarChar(1024) ) as NodePath
    from@MyTableWithFourColumnswhere ParentId isNULLunionallselect MTWFC.Id, MTWFC.ParentId, MTWFC.NodeText, T.Depth +1, Cast( Space( 2* ( T.Depth +1 ) ) + MTWFC.NodeText asVarChar(100) ) as IndentedNodeText,
    Cast( T.NodePath +' > '+ MTWFC.NodeText asVarChar(1024) ) as NodePath
    from Tree as T innerjoin@MyTableWithFourColumnsas MTWFC on MTWFC.ParentId = T.Id )
  select*from Tree
    orderby NodePath;