title: iterate through XML sql insert
body: <p>I have three tables <code>table1, table2, table3</code> with <code>col1, col2</code> and identity <code>ID</code> column. These table relationship are defined in the database.</p>
  
  <p>I am trying to create a stored procedure that accepts xml string input and save that data into tables.</p>
  
  <p>This is the XML input</p>
  
  <pre><code>&lt;root&gt;
   &lt;table1 col1='a' col2='b'&gt;
    &lt;table2Array&gt;
     &lt;table2 col1='c' col2='d'&gt;
      &lt;table3array&gt;
       &lt;table3 col1='g' col2='h' /&gt;
       &lt;table3 col1='i' col2='j' /&gt;
      &lt;/table3array&gt;
     &lt;/table2&gt;
    &lt;table2 col1='c' col2='d'&gt;
     &lt;table3array&gt;
      &lt;table3 col1='k' col2='l' /&gt;
      &lt;table3 col1='i' col2='j' /&gt;
     &lt;/table3array&gt;
    &lt;/table2&gt;
   &lt;/table2Array&gt;
  &lt;/table1&gt;
   &lt;table1 col1='a' col2='b'&gt;
    &lt;table2Array&gt;
     &lt;table2 col1='e' col2='f'&gt;
      &lt;table3array&gt;
       &lt;table3 col1='i' col2='j' /&gt;
       &lt;table3 col1='i' col2='j' /&gt;
      &lt;/table3array&gt;
     &lt;/table2&gt;
     &lt;table2 col1='e' col2='f'&gt;
      &lt;table3array&gt;
       &lt;table3 col1='g' col2='h' /&gt;
       &lt;table3 col1='g' col2='h' /&gt;
      &lt;/table3array&gt;
     &lt;/table2&gt;
    &lt;/table2Array&gt;
   &lt;/table1&gt;
  &lt;/root&gt;
  </code></pre>
  
  <p>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.</p>
  
  <p>Algorithm:</p>
  
  <ol>
  <li>Loop through each node</li>
  <li>Insert node attribute into table</li>
  <li>Get last identity value</li>
  <li>Call child nodes with last identity value as foreign key</li>
  <li>Do until no more child nodes</li>
  </ol>
  
  <p>Is this the only way to handle this situation? If so how to iterate through xml node?</p>
  
  <p>Please help!!</p>
  
  <p>Thanks,</p>
  
  <p>Esen</p>