Re: Bulk Load schema problems
From: Chandra Kalyanaraman [MSFT] (chandrak_at_online.microsoft.com)
Date: 10/21/04
- Previous message: Chandra Kalyanaraman [MSFT]: "Re: SQLXMLBulkLoad to SQL Server behind firewall"
- In reply to: Luciano Pagliarini: "RE: Bulk Load schema problems"
- Messages sorted by: [ date ] [ thread ]
Date: Thu, 21 Oct 2004 14:03:31 -0700
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... > 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
- Previous message: Chandra Kalyanaraman [MSFT]: "Re: SQLXMLBulkLoad to SQL Server behind firewall"
- In reply to: Luciano Pagliarini: "RE: Bulk Load schema problems"
- Messages sorted by: [ date ] [ thread ]
Relevant Pages
|
