Bulk Load schema problems

Hi, I'm from Brazil, so sorry bad english
I have a xml document like this:
<competition name="Formula one" year="2003"> <!-- this is the root element
-->
<race number="1" local="Brazil">
<pilots number_pilots="10">
<pilot name="schummacker" pos="1" team="ferrari"/>
<pilot name="barriquelo" pos="2" team="ferrari"/>
No help for me?
"Luciano Pagliarini" wrote:

> Hi, I'm from Brazil, so sorry bad english
> I have a xml document like this:
> <competition name="Formula one" year="2003"> <!-- this is the root element
> -->
> <race number="1" local="Brazil">
> <pilots number_pilots="10">
> <pilot name="schummacker" pos="1" team="ferrari"/>
> <pilot name="barriquelo" pos="2" team="ferrari"/>
> .
> .
> .
> </pilots>
> <teams number_teams="5">
> <team name="ferrari" points="18"/>
> <team name="willians" points="9"/>
> .
> .
> .
> </teams>
> <totals finished_pilots="8" out_pilots="2" accidents="1"/>
> </race>
> </competition>
>
> ps.: I will have one file for each race. I need import this to sql server
> using xml bulk load and defining xsd schema. I need fast import.
> the schema that i made, actualy works to import, but have some problems
>
> 1) I have a table named "competitions" mapped to the element "competition"
> of the xml documents. Always i run XMLBulkLoad.execute one record to this
> element is placed in the table. If i have 500 documents to be read, 500
> records with the same content will be placed in this table. (I make a
> workarround make one file with a union of the small files, puting the element
> just one time. this is a workarround that consume time, and the time to load
> for me is important)
> 2) If a have a table named "pilots_results" with this layout:
> "year" , "race_id" , "pilot_name" , "position"
> how can I construct a schema that import JUST this data to ONE table, and
> nothing more. (the error is like the element "race" have no item associated
> with it, or some like this. sory I dont remenber)
> 3) Now, if i have a table named "races" with this layout:
> "year" , "race_id" , "finished_Pilots" , "out_pilots" , "accidenst"
> how can I construct schema to import this way?
> note that the node "totals" is a subelement of the element "race", but is
> totaly associated, because is totals for the especific race!
> acctualy a have to create a table named "races_totals" just to put this
> data, mapping the element "totals" to this table. note that for each
> "race_id" in that "year" I will have just one "totals" element.
> 4) and, finally, the performanse...
> for 500 xml documents about 50kb each one, the time to import is ~4minutes
> or more.
> the tables are off indexses, no transaction is used, no users access the
> database, just the normal windows services running,
>
> anybody can help? this is very urgent for me, I have a delivery stated
> period for this system and can't see the light in the end of tunel.
> thanks for all
> Luciano Pagliarini
|||Hi Luciano,
For
1) I don't see any other way, may be you can run bulkload.execute in a loop
over all your data files
2) you can define schema that has a relationship between competition and
race and a relationship between race and pilot_results
that way you can propagate ID from competition to race to pilot_results
or
you just have year and raceid repeated for every pilot_results
<pilot name="schummacker" pos="1" team="ferrari" year="2003" raceid="1"/>
3) similar to (2)
4) I do not understand your question here. If you are looking for ways to
increase perf, try not using a transaction, keepidentity=<default value> and
no schemagen
HTH,
-Chandra
This posting is provided "AS IS" with no warranties, and confers no rights.
Use of included script samples are subject to the terms specified at
http://www.microsoft.com/info/cpyright.htm
"Luciano Pagliarini" <LucianoPagliarini@.discussions.microsoft.com> wrote in
message news:3DCA9725-CA12-4997-99AB-E1334D692424@.microsoft.com...[vbcol=seagreen]
> No help for me?
> "Luciano Pagliarini" wrote: