Sqlbulkcopy to write datatable contents to SQL server database.

See more:

C#

.NET

All,


I am reading data from an XMl file and fetching this data into a datatable.
Next I am using SQLBulkCopy for writing the contents of this datatable to a SQL Server DB table.

Now, my XML file has a varying number of columns. Therefore the data gets inserted into the DB table but the column values are not aligned
e.g.


However my XML data may have columns like ID,Currency, Status, Value, SpotRateIdentifier or at times like
ID,Currency, Status, Value or ID,Currency, Status.


Any help would be much appreciated.

What I have tried:

StringReader theReader = new StringReader(xmlData);
DataSet theDataSet = new DataSet();
theDataSet.ReadXml(theReader);

using (SqlBulkCopy bulkCopy = new SqlBulkCopy(connection))
{
    bulkCopy.DestinationTableName = 
        "dbo.ExistingSqlTableName";

    try
    {
        // Write from the source to the destination.
        bulkCopy.WriteToServer(theDataSet.Tables["ExistingSqlTableName"]);
    }
    catch (Exception ex)
    {
        Console.WriteLine(ex.Message);
    }
}