StackOverflow2013

Note that there are some explanatory texts on larger screens.

plurals

POInsert & Update SQL Table from XML file data using VB.NET 2010
text
Body
copied!<p>I have been researching and writing/re-writing a program to do this task for a week now. I need some collaboration on this to maybe bring up something I haven't though of before. Specifically, we have an auto-generated XML file sent to us daily with ~70k records (~75MB in size.) I was asked to make a table on one of the servers (SQL) which contains this information so that it can queried. Also, this program must Update existing records (if data has changed) and Insert new records DAILY. <strong>Records must not be deleted from the db</strong> </p> <p>Here is the list of methods I have attempted (so far) and reasons they did not work.</p> <ol> <li><p>SQLXMLBulkLoad - This worked excellent for importing the data. However, the limitation of the Bulk Load class is that it can not Update and/or Insert. Time for a re-write.</p></li> <li><p>SQL OpenRowSet (using SQLCommand, etc.) - This does not work because the server, program, and XML file will all 3 be on different computers. These devices CAN be configured to allow each other access to the file (specifically the server), however this method was deemed "Not realistic, too much overhead" Time for a re-write.</p></li> <li><p>DataSet Merge, then TableAdapter.Update - This method intitially seemed like it would definitely work. The idea is simple, use DataSet.XMLRead() method to put the XML data into a table in the dataset, then just add the SQL table to the dataset (Using SQLCommand, etc.), merge the two tables, and then use Table Adapter to Update/Insert the table into the existing SQL table. This method seems not to work because the XML file has two nodes (columns) which contains dates. Unfortunately, there is not a uniform Date datatype between SQL and XML. I even attempted changing all of the date formats from the XML file to the DateTime SQL format, which worked, but still deemed a datatype mismatch exception upon running.</p></li> </ol> <p>At this point, I am out of ideas. This seems to be a task that has surely been done before. I am not necessarily looking for someone to write this code for me (I am fully capable of this), I just need some collaboration on the topic.</p> <p>Thank You </p>

Querying!

Guidance

An individual column

Larger individual text columns get their own page to allow for proper reading.

SQuiL has stopped working due to an internal error.

If you are curious you may find further information in the browser console, which is accessible through the devtools (F12).

Reload