title: SQL Server 2005 Insert parent/child xml data
body: <p>Given an xml document that looks like this here:</p>
  
  <pre><code>  &lt;parentRecords&gt;
      &lt;parentRecord field1="foo" field2="bar"&gt;
        &lt;childRecord field1="test" field2="text" /&gt;
        &lt;childRecord field1="test2" field2="text2" /&gt;
      &lt;/parentRecord&gt;
      &lt;parentRecord field1="foo2" field2="bar2"&gt;
        &lt;childRecord field1="test3" field2="text3" /&gt;
        &lt;childRecord field1="test4" field2="text4" /&gt;
      &lt;/parentRecord&gt;
    &lt;/parentRecords&gt;
  </code></pre>
  
  <p>What would be the fastest way in SQL Server 2005 to pass this document into a stored procedure which would insert the parent and child records into a set of tables, where the parent table has an identity column, and the child table refers to the parent by a foreign key?</p>
  
  <pre><code>ParentTable
  -----------
  ParentID identity PK int
  Field1 nvarchar
  Field2 nvarchar
  
  ChildTable
  ----------
  ChildID identity PK int
  ParentID FK int
  Field1 nvarchar
  Field2 nvarchar
  </code></pre>
  
  <p>I'm using ADO.NET and .NET 3.5 with C#.  I have the option of sending the data as an xml parameter type or a text type.  I can use the new-fangled sql 2005 XQuery stuff or the oldschool SQL 2000 OPENXML style.  Or if it's actually possible to accomplish these inserts using SqlBulkInsert or something like that, I'm down with whatever is the fastest (performance is important in this situation.)  Thanks for your help!</p>
  
  <hr>
  
  <p><strong>EDIT:</strong></p>
  
  <p>Looks like inserting parent/child sets is indeed as difficult as it seems.  I was not in a position to try learning LINQ to SQL and integrating that framework into my product (we're in a dev cycle here!) and I wasn't able to get much traction with the Xml Bulk Insert tool although it appears it could be used for this purpose.  In the end I restructured the tables to use GUID primary keys on both tables, and generated the full records in the API.  Then I was able to use ADO 2.0 SqlBulkInsert to send the data down at high speed.  Answer awarded to Daniel Miller because SQL Server Bulk Load had the best chance of success without re-architecting my application altogether.</p>