Import from XML to SQL Server 2000
-
We needing th ability to import large amounts of data on a nightly basis into SQL Server 2000. The management has chosen to use XML as the "conduit" through which we will get the data. The next question is now how to best import from the several XML files we will receive into the db? Any thoughts?
--mjnish
-
See this link http://www.sqlservercentral.com/forums/shwmessage.aspx?forumid=21&messageid=184423#bm184434
------------------------------
The Users are always right - when I'm not wrong! -
Convince whoever made that decision of the stupidity of using XML to move "large amounts of data." Small messages are one thing, but the bloat of XML when transfering large amounts of data is ridiculous. If you have 1 MB of data that is then magically transformed into 7MB via the magic of XML, what good is that?
See http://www.sqlservercentral.com/columnists/dpeterson/isxmltheanswer.asp for a more detailed explanation.
/*****************
If most people are not willing to see the difficulty, this is mainly because, consciously or unconsciously, they assume that it will be they who will settle these questions for the others, and because they are convinced of their own capacity to do this. -Friedrich August von Hayek
*****************/
-
Well, the die has been cast pertaining to using XML to import the data. So, though, I have already expressed concern about this, nothing can now be done. XML schemas are already being created for the customer to use...
Now we have to figure out how to get the XML data into SQL Server. Some data will be able to merely be inserted directly, while other data will have to be updated and/or manipulated. So this is not merely a bulk insert sort of thing. The suggestion from management is to use a web or windows service and C# to handle everything--the validation of the data and then even a sp call per record imported. This sounds awful to me. It would seem better to, at most, use the C# to handle the validation of data against the schemas--but then to call sps or dts to let all the actual data import/merge be handled within sql server. Any thoughts?
-
I can assure you that if the amount of data is large enough, your one sp call per record is going to be VERY SLOW!!!!!!!!!
You definitely need som sort of BULKINSERT or bcp to achieve high performance. You could try to use OPENXML and manipulate data in a temp table but again, provided that the amount of data is not trivial, you are going to have problems with it.
I hope this is another proof that xml is not the tool for the job.
* Noel -
I've tried and used the SQLXML Bulk Load component provided by Microsoft with much success. It performs comparitively as fast as BCP and enables you to insert child related records in multiple tables which you cannot do with flat file.
The best example and explanation I can find is here
http://www.sqljunkies.com/Article/650DF949-148D-46B3-9156-6783646F976D.scuk
-
Since you have to manipulate data, what you can do ( which I did too) TRANSFORM your XML files into smaller xml files and during transformation manipulate your data. Than use OPENXML to load your data directly or even can be BULKLOAD.
-
Wow, I guess I've been doing the impossible for years! I need to ask for a raise!
/*****************
If most people are not willing to see the difficulty, this is mainly because, consciously or unconsciously, they assume that it will be they who will settle these questions for the others, and because they are convinced of their own capacity to do this. -Friedrich August von Hayek
*****************/
Viewing 8 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply