SQL Server 2005 Insert parent/child xml data

Question

Given an xml document that looks like this here:

  <parentRecords>
    <parentRecord field1="foo" field2="bar">
      <childRecord field1="test" field2="text" />
      <childRecord field1="test2" field2="text2" />
    </parentRecord>
    <parentRecord field1="foo2" field2="bar2">
      <childRecord field1="test3" field2="text3" />
      <childRecord field1="test4" field2="text4" />
    </parentRecord>
  </parentRecords>

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?

ParentTable
-----------
ParentID identity PK int
Field1 nvarchar
Field2 nvarchar

ChildTable
----------
ChildID identity PK int
ParentID FK int
Field1 nvarchar
Field2 nvarchar

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!

---

EDIT:

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.

Answer 1

Consider LINQ to SQL. It will actually simplify you on this one.

Answer 2

Sounds like you need SQL Server XML Bulk Load

Answer 3

Something like this will extract parents followed by children with parent fields

DECLARE @fooxml xml

SET @fooxml = N'<parentRecords>
    <parentRecord field1="foo" field2="bar">
      <childRecord field1="test" field2="text" />
      <childRecord field1="test2" field2="text2" />
    </parentRecord>
    <parentRecord field1="foo2" field2="bar2">
      <childRecord field1="test3" field2="text3" />
      <childRecord field1="test4" field2="text4" />
    </parentRecord>
  </parentRecords>'

SELECT
    x.item.value('@field1', 'varchar(100)') AS field1,
    x.item.value('@field2', 'varchar(100)') AS field2
FROM
    @fooxml.nodes('/parentRecords/parentRecord') x(item)

SELECT
    x.item.value('@field1', 'varchar(100)') AS field1,
    x.item.value('@field2', 'varchar(100)') AS field2,
    y.item.value('@field2', 'varchar(100)') AS childfield2,
    y.item.value('@field2', 'varchar(100)') AS childfield2
FROM
    @fooxml.nodes('/parentRecords/parentRecord') x(item)
    CROSS APPLY
    x.item.nodes('./childRecord') AS y(item)

Answer 4

Hello, If my XML is like below - how can I achieve the same result?

<Details><Personal><Name>MyName</Name></Personal></Details>