If been trying to update a table in my SQL server 2000 database with data from an xml file. I've tried a load of different ideas but can't seem to find the best way in.
I eventually went for SqlBulkCopy which seemed to be the fastest way of loading a mass amount of data into SQLServer (SQLXMLBULKLOADLib doesn't appear to like dotnet 2.0)
So here is how I'm coding it
So, this seems to work alright and having discovered that itís not possible to simple pass a dataset full of all my xml records I decided that I should do the update one record at a time, thus;
SqlConnection thisSQLConn = new SqlConnection(connectionstring);
SqlBulkCopy thisBC = new SqlBulkCopy(thisSQLConn);
DataSet thisDS = new DataSet();
thisBC.DestinationTableName = "sqltable";
Well, apparently you can't use foreach statements like this
foreach (DataTable table in dsStore.Tables["xmlparentnode"])
I'm a little stuck for a good way to...
foreach statement cannot operate on variables of type 'system.data.datatable'
1. Input my xml data without having to code every single column insert
2. Insert multiple records into the database
Here is the format of my xml