XML Insert with 0 or more child records

Question

XML Insert with 0 or more child records

Steve

SSCommitted

Points: 1844
More actions
May 10, 2006 at 3:31 am

#68798

Hello All

I have an interesting project on which I would like to solicit your opinion. I have a dataset modeled below in XML. Basically there is a parent record which may have 0 or more child records. I have to find the fastest way to get this information into the database, keeping in mind that the dataset could be on the order of 500k records. Here are the requirements for this project.
      1. The Parent.TransactionID is a primary key in the XML, however in the Parent table in the database, this column is an identity int field and as such will be autogenerated.
      2. The Child.TransactionID is modeled in the database just as it is in this XML in that it is a foreign Key to the Parent table.
      3. The destination for this dataset is a SQL 2005 Database.

I have a solution, but realizing that others might have better ideas than me, I wanted to get your opinion on how you would accomplish the same task.

Thanks in advance for your time.
Steve

<BatchInfo>
<Parent>
    <ProductID>2</ProductID>
    <CreateDate>2005-05-16T00:00:00Z</CreateDate>
    <TransactionID>0</TransactionID>
</Parent>
<Parent>
    <ProductID>1</ProductID>
    <CreateDate>2005-05-11T00:00:00Z</CreateDate>
    <TransactionID>1</TransactionID>
</Parent>
<Child>
    <TransactionID>0</TransactionID>
    <ExtendedInfo>Memo Extended desc 1</ExtendedInfo>
    <SequenceNumber>1</SequenceNumber>
</Child>
<Child>
    <TransactionID>0</TransactionID>
    <ExtendedInfo>Memo Extended desc 2</ExtendedInfo>
    <SequenceNumber>2</SequenceNumber>
</Child>
<Child>
    <TransactionID>0</TransactionID>
    <ExtendedInfo>Memo Extended desc 3</ExtendedInfo>
    <SequenceNumber>3</SequenceNumber>
</Child>
</BatchInfo>
Steve

Viewing 4 posts - 1 through 3 (of 3 total)

You must be logged in to reply to this topic. Login to reply

peterhe SSChampion Points: 11469 More actions · Answer 1

you can use OPENXML to shred parent and child data into two temp table. Then you can handle the parent and child relation quickly.

Steve SSCommitted Points: 1844 More actions · Answer 2

I had thought of that, but wouldn't the overhead caused by inserting the data into two temp tables be greater than doing a direct row by row insert directly from an OPENXML statement?

Steve

peterhe SSChampion Points: 11469 More actions · Answer 3

You can insert into parent table directly if you do not need more processing. Since the TransactionID is an identity in the parent table, you need to get the new ID and associate it with child data. If you have columns other than TransactionID are unique in the xml, it's easy to do so.