C# console app. SQLBulkCopy and quickly entering XML into a SQL Server DB-Collection of common programming errors

C# with .net 2.0 with a SQL server 2005 DB backend.

I’ve a bunch of XML files which contain data along the lines of the following, the structure varies a little but is more or less as follows:

  
    All current tanks 
    Tank is close to capacity 
     
     
     
  

I have a single DB table that has all the above fields ready to be filled.

When I use the following method of reading the data from the XML file:

DataSet reportData = new DataSet();
reportData.ReadXml("../File.xml");

It successfully populates the Dataset but with multiple tables. So when I come to use SQLBulkCopy I can either save just one table this way:

sbc.WriteToServer(reportData.Tables[0]);

Or if I loop through all the tables in the Dataset adding them it adds a new row in the Database, when in actuality they’re all to be stored in the one row.

Then of course there’s also the issue of columnmappings, I’m thinking that maybe SQLBulkCopy is the wrong way of doing this.

What I need to do is find a quick way of getting the data from that XML file into the Database under the relevant columns in the DB.