| jbuedel@gmail.com 2006-01-11, 1:23 pm |
| I am trying to use Xml Bulk Load (via SQLXML3) to load some very
structured data.
Is it possible to cascade insert generated identity columns
when the table has multiple parents?
I have three tables that look like so:
Claims (id, fkOriginalFileId, SubmissionDate, FormType, fkProvider)
Provider(id, Name, City)
OriginalClaimsFiles (id, DateReceived, FileType)
The Claims table has two foreign keys. One pointing to Provider and
one pointing
to OriginalClaimsFiles. All columns named 'id' are autogenerated
identity columns.
I need the sql generated id values from inserting Provider and
OriginalClaimsFiles before
I can insert the Claims row.
Some example input xml follows. I got this xml by calling
DataSet.WriteXml(),
if it matters.
<ClaimsDataSet xmlns="http://tempuri.org/ClaimsDataSet.xsd">
<Claims>
<id>-1</id>
<fkOriginalFileId>-1</fkOriginalFileId>
<SubmissionDate>2006-01-09</SubmissionDate>
<FormType>P</FormType>
<fkProvider>-1</fkProvider>
</Claims>
<Provider>
<id>-1</id>
<Name>John Doe</Name>
<City>Saint Louis</City>
</Provider>
<OriginalClaimsFiles>
<id>-1</id>
<DateReceived>2006-01-09</DateReceived>
<FileType>837P</FileType>
</OriginalClaimsFiles>
</ClaimsDataSet>
I have been able to get a single auto generated id to cascade to the
child by
physically nesting the child record in the parent, like so:
<ClaimsDataSet xmlns="http://tempuri.org/ClaimsDataSet.xsd">
<Provider>
<id>-1</id>
<Name>John Doe</Name>
<City>Saint Louis</City>
<Claims>
<id>-1</id>
<fkOriginalFileId>-1</fkOriginalFileId>
<SubmissionDate>2006-01-09</SubmissionDate>
<FormType>P</FormType>
<fkProvider>-1</fkProvider>
</Claims>
</Provider>
</ClaimsDataSet>
Unfortunately the <OriginalClaimsFiles> element is left out of the
party, as I can't
locate my <Claims> element under multiple parents.
I'm not going to bother you with a copy of the XSD. Since the above
example works
I assume it's correct.
So, to restate, can I cascade auto generated identity values through
multiple
foreign key relationships when using Xml Bulk Load?
---- More Background (In case you want to tell me I'm going about this
all wrong) ---
My data originally comes from a complex typed DataSet. It has around
30 tables with
many foreign key relationships between them. Every table has an
autogenerated
identity column named 'id'. Many of these datasets are created
throughout the
day (over 1000), each averaging 2500 total rows. The datasets are
populated from
flat files that we receive throughout the day.
To get the data into sql thus far I've done individual inserts via a
SqlCommand
object. I pass each DataTable to a DataAdapter with a custom crafted
InsertCommand,
in order starting with the "highest" parent down through the children.
The inserted
row's identity and all dependent foreign keys are updated on the client
via the
combination of an Output parameter that returns the freshly inserted
identity value
and the cascade updates feature of dataset relationships.
This works correctly as far as properly maintaining all the
relationships when the
data is inserted. Unfortunately it's just too slow and now I'm casting
about looking
for alternatives.
I would truly be appreciative of any guidance one can provide.
Thanks!
Josh
|