iterate through XML sql insert

Question

I have three tables table1, table2, table3 with col1, col2 and identity ID column. These table relationship are defined in the database.

I am trying to create a stored procedure that accepts xml string input and save that data into tables.

This is the XML input

<root>
 <table1 col1='a' col2='b'>
  <table2Array>
   <table2 col1='c' col2='d'>
    <table3array>
     <table3 col1='g' col2='h' />
     <table3 col1='i' col2='j' />
    </table3array>
   </table2>
  <table2 col1='c' col2='d'>
   <table3array>
    <table3 col1='k' col2='l' />
    <table3 col1='i' col2='j' />
   </table3array>
  </table2>
 </table2Array>
</table1>
 <table1 col1='a' col2='b'>
  <table2Array>
   <table2 col1='e' col2='f'>
    <table3array>
     <table3 col1='i' col2='j' />
     <table3 col1='i' col2='j' />
    </table3array>
   </table2>
   <table2 col1='e' col2='f'>
    <table3array>
     <table3 col1='g' col2='h' />
     <table3 col1='g' col2='h' />
    </table3array>
   </table2>
  </table2Array>
 </table1>
</root>

This xml is coming from a third party object and we don't have control to modify the third party object to emit a different format xml.

Algorithm:

1. Loop through each node
2. Insert node attribute into table
3. Get last identity value
4. Call child nodes with last identity value as foreign key
5. Do until no more child nodes

Is this the only way to handle this situation? If so how to iterate through xml node?

Please help!!

Thanks,

Esen

Answer 1

Using merge and output you can do this without a loop using a technique described here.

I assume your table structure is like this.

create table Table1
(
  Table1ID int identity primary key,
  Col1 char(1),
  Col2 char(1)
)

create table Table2
(
  Table2ID int identity primary key,
  Table1ID int references Table1(Table1ID),
  Col1 char(1),
  Col2 char(1)
)

create table Table3
(
  Table3ID int identity primary key,
  Table2ID int references Table2(Table2ID),
  Col1 char(1),
  Col2 char(1)
)

The code

declare @T1 table (XMLCol xml, TargetID int);
declare @T2 table (XMLCol xml, TargetID int);

merge Table1 as T
using (select T1.XMLCol.query('*'),
              T1.XMLCol.value('@col1', 'char(1)'),
              T1.XMLCol.value('@col2', 'char(1)')
       from @XML.nodes('/root/table1') as T1(XMLCol)) as S(XMLCol, Col1, Col2)
on 1 = 0
when not matched then
  insert (Col1, Col2) values (S.Col1, S.Col2)
output S.XMLCol, inserted.Table1ID into @T1;          

merge Table2 as T
using (select T2.XMLCol.query('*'),
              T1.TargetID,
              T2.XMLCol.value('@col1', 'char(1)'),
              T2.XMLCol.value('@col2', 'char(1)')
       from @T1 as T1  
         cross apply T1.XMLCol.nodes('table2Array/table2') as T2(XMLCol)) as S(XMLCol, ID1, Col1, Col2)
on 1 = 0
when not matched then
  insert (Table1ID, Col1, Col2) values (S.ID1, S.Col1, S.Col2)
output S.XMLCol, inserted.Table2ID into @T2;          

insert into Table3(Table2ID, Col1, Col2)
select T2.TargetID,
       T3.XMLCol.value('@col1', 'char(1)'),
       T3.XMLCol.value('@col2', 'char(2)') 
from @T2 as T2
  cross apply T2.XMLCol.nodes('table3array/table3') as T3(XMLCol);

SE-Data (select "Text-only results" to see all resultsets)

Answer 2

Thought it would be helpful to someone looking for similar solution. This is how I handled traversing nodes in SQL

        declare @xmlRoot as xml 
        set @xmlRoot= '<root>
         <table1 col1="a" col2="b">
          <table2Array>
           <table2 col1="c" col2="d">
            <table3array>
             <table3 col1="g" col2="h" />
             <table3 col1="i" col2="j" />
            </table3array>
           </table2>
          <table2 col1="c" col2="d">
           <table3array>
            <table3 col1="k" col2="l" />
            <table3 col1="i" col2="j" />
           </table3array>
          </table2>
         </table2Array>
        </table1>
         <table1 col1="a" col2="b">
          <table2Array>
           <table2 col1="e" col2="f">
            <table3array>
             <table3 col1="i" col2="j" />
             <table3 col1="i" col2="j" />
            </table3array>
           </table2>
           <table2 col1="e" col2="f">
            <table3array>
             <table3 col1="g" col2="h" />
             <table3 col1="g" col2="h" />
            </table3array>
           </table2>
          </table2Array>
         </table1>
        </root>'
        Declare @col1 varchar(100),@col2 varchar(100), @table1Counter int, @table2Counter int

        select @table1Counter=0

        DECLARE table1_cursor CURSOR FOR 
            SELECT
            col1 =  item.value('./@col1', 'varchar(100)'), 
            col2 =  item.value('./@col2', 'varchar(100)') 
            FROM @xmlRoot.nodes('root/table1') AS T(item);

            OPEN table1_cursor

            FETCH NEXT FROM table1_cursor 
            INTO  @col1 ,@col2

            WHILE @@FETCH_STATUS = 0
                BEGIN
                    --insert into table1 and get id into a variable
                     set @table1Counter=@table1Counter+1


                    DECLARE table2_cursor CURSOR FOR 
                    SELECT
                    col1 =  item.value('./@col1', 'varchar(100)'),
                    col2 = item.value('./@col2', 'varchar(100)') 
                    FROM @xmlRoot.nodes('root/table1[sql:variable("@table1Counter")]/table2Array/table2') AS T(item);                            
                        OPEN table2_cursor
                        FETCH NEXT FROM table2_cursor INTO @col1 ,@col2                       
                        WHILE @@FETCH_STATUS = 0
                            BEGIN
                                --insert into table2 and get id into a varialbe
                                set @table2Counter = @table2Counter+1

                                 --do same for table3 similar to table2

                                FETCH NEXT FROM table2_cursor INTO @col1 ,@col2
                             END
                        CLOSE table2_cursor
                        DEALLOCATE table2_cursor                            
                    FETCH NEXT FROM table1_cursor 
                    INTO  @col1, @col2                      
                END 
        CLOSE table1_cursor;
        DEALLOCATE table1_cursor;

Answer 3

If your code sample is representative of the kind of data you get, and strictly adheres to a consistent structure, you could try making a class to deserialize. Here is an example set of classes that will correctly deserialize from your given XML sample:

[XmlRoot("root")]
public class MyCustomStructure
{
    [XmlElement("table1")]
    public Table1Structure[] Table1Array { get; set; }
}

[XmlRoot("table1")]
public class Table1Structure
{
    [XmlAttribute("col1")]
    public string Col1 { get; set; }
    [XmlAttribute("col2")]
    public string Col2 { get; set; }
    [XmlArray("table2Array")]
    [XmlArrayItem("table2")]
    public Table2Structure[] Table2Array { get; set; }
}

[XmlRoot("table2")]
public class Table2Structure
{
    [XmlAttribute("col1")]
    public string Col1 { get; set; }
    [XmlAttribute("col2")]
    public string Col2 { get; set; }
    [XmlArray("table3array")]
    [XmlArrayItem("table3")]
    public Table3Structure[] Table3Array { get; set; }
}

public class Table3Structure
{
    [XmlAttribute("col1")]
    public string Col1 { get; set; }
    [XmlAttribute("col2")]
    public string Col2 { get; set; }
}

Example code that will apply the deserialization:

var ser = new XmlSerializer(typeof(MyCustomStructure));

// if xml is in a string, use the following:
var sr = new StringReader(xml);
var xr = new XmlTextReader(sr);

// if xml is in a stream, use the following:
var xr = new XmlTextReader(stream);

// if xml is in an XmlElement, use the following:
var xr = new XmlNodeReader(element);

// result contains an instance of MyCustomStructure
var result = ser.Deserialize(xr);

From here, it's as simple as looping through the contents of MyCustomStructure with for each loops and applying your custom database insert logic:

for each (var table1 in result.Table1Array)
{
    // insert table1, get inserted ID
    for each (var table2 in table1.Table2Array)
    {
        // insert table2, use table1 inserted ID, get table2 ID
        for each (var table3 in table2.Table3Array)
        {
            // insert table3, use table2 inserted ID
        }
    }
}

If you're worried about performance with the scale of data you are inserting, you can try passing the data as table-valued parameters, or some other format that you can easily parse on the SQL side. You can also bulk upload all the table1 entries, get all the ID's back, then bulk upload all the table2 entries with the correct mapped ID's, get all the new ID's back, etc., which would require a total of 3 or so round trips and should be pretty fast.