Importing From XML

Hello everyone and thanks in advance.

Ok, here's the deal. I am 1 of 2 developers at my organization, the other is a COBOL programmer. Long story short, every night he sends me a 22MB XML file containing our company's inventory. This contains about 40,000 items. In C#, I'm basically looping through all the XML nodes and on each node I load up an object with the particular properties (manufacturer, SKU, serial number, etc.) and call a Stored Procedure to to save the item.

In saving the item, I'm checking to see if it was already in my inventory and I either perform an update or an insert on the table as approriate. The query does a couple of other things like retreiving the Purchase Order Number, Cubic Feet, etc. from other tables. All in all, the query takes about 1-2 seconds to complete. I've optimized the query as much as I know how (including the addition of indexes), but this still takes at least 6 hours to complete the 40,000 items. Is this the best approach? Any other suggestions?

So far the only things I've thought about changing is:
1. Break it out into multiple queries to avoid poor compilation of execution plans

2. Multithread the C# app so that I'm running like 10 queries at once.

3. Get the COBOL programmer to give me a delimited file and do a BULK INSERT on it into an empty HEAP, then run a single INSERT and a single UPDATE (using CASE statements)

Again, Thanks in advance!I like your ideas, but I'd tacke them in reverse order (3, 2, 1).

-PatP|||Why don't you try to bulk insert XML using SQLXML?

http://msdn.microsoft.com/library/default.asp?url=/library/en-us/sqlxml3/htm/bulkload_7pv0.asp?frame=true

Once you have the data imported into the database you can use DTS to do everything.|||What does XML and the relational model have in common?

Absolutley nuthin

say it again

XML, what is it good for....

EDIT: That deserves a BIG MOO|||Not sure if I see this correctly, but your processing the nodes one by one? Could it help if you'd compile the xml into an appropriate object structure, see what's new and then have a go at saving it?sql

Importing binary data tables?

My apologies in advance for hitting the forum with such a basic question...

I've got a large number of raw binary data tables generated by some external software that I'd like to import into a SQL database. I've set up the database tables to hold the information and assigned the table columns the appropriate data types, but I can't figure out how to import the data.

From what I can see there isn't a data import 'wizard' that supports raw binary, and my limited experience with bcp has given me the impression that it only supports ASCII tables. I find it hard to believe that there isn't a DTS procedure to handle this... but I'm definitely stuck...

Thanks,
-fpsivYou can import data by using bcp or bulk insert to text or image fields and then parse data if it needs.|||Thanks for the quick reply snail...

I was working with BULK INSERT via the Query Analyzer... but ran into a brick wall in trying to set up the format file. I can't seem to get the 'column collation' parameter set correctly. I thought that 'collation' applied to character data... is there a correct collation for raw binary?

Thanks,
-fpsiv|||Check this : import image, I was trying to the same for binary file - it works too.

http://www.dbforums.com/t926676.html|||Thanks snail... got it working! I really appreciate your help!

Cheers
-fpsiv|||hmmml ! I tried the same query but it didn't worked :

CREATE TABLE Pictures
(
Pic_id smallint
IDENTITY(1,1)
PRIMARY KEY CLUSTERED,
Images nvarchar(5000)
)

BULK INSERT Northwind.dbo.[pictures] FROM 'c:\bridge.jpg'
WITH (
FORMATFILE = 'c:\bcp.fmt'
)

------------
Server: Msg 156, Level 15, State 1, Line 1
Incorrect syntax near the keyword 'from'.

Did I missing something?

Thanks