title: Bulk insert into SQL Server 2008 R2 from XML RAW data
body: <p>I have an XML structure like the following:</p>
  
  <pre><code>&lt;tables&gt;
    &lt;table name="tableName1"&gt;
      &lt;row ID="34" col1="data" col2="dom" /&gt;
      &lt;row ID="35" col1="data2" col2="dom2" /&gt;
    &lt;/table&gt;
    &lt;table name="tableName2"&gt;
      &lt;row ID="1" col1="data" col2="dom" col3="item1" /&gt;
      &lt;row ID="3" col1="data2" col2="dom2" col3="item2" /&gt;
      &lt;row ID="7" col1="data4" col3="item3" /&gt;
    &lt;/table&gt;
    ...
  &lt;tables&gt;
  </code></pre>
  
  <p>Basically the table nodes contain RAW data created by selecting <code>FOR XML RAW</code>.</p>
  
  <p>Now I wish to do the reverse: read the XML and insert data into respective tables of a SQL Server 2008 R2 database. However I want the loading process to be robust, meaning I do not want to mess with column names and table names if they change in the future. I need the process to read table names from <code>@name</code> attributes of table nodes and insert data into columns specified by attributes in <code>&lt;Row&gt;</code> nodes. I thought of a stored procedure that gets an XML as input and does the rest.</p>
  
  <p>The amount of data is approx. 70 tables ranging from 10 to 30 000 rows, altogether no more than 100 000 rows. I need to do it as efficiently as possible, bulk loading would be the best.</p>
  
  <p>The process should not take care of foreign keys as the order of tables inside the XML is built so that FK constraints can be kept in place by loading one table after the other. </p>
  
  <p>However there are identity columns in each table so I must do a</p>
  
  <pre><code>SET Identity_Insert ON and SET Identity_Insert OFF
  </code></pre>
  
  <p>before and after processing each table. I also need to reseed each table after inserting all rows. Oh,and I need to do the whole shebang in a transaction so that I could roll back if something goes wrong.</p>
  
  <p>Which way do you suggest I go: should I stay with T-SQL or try to write the SP in CLR SQL? Should I use XQuery or can I use some bulk insert method?</p>
  
  <p>Thanks for all the help!</p>