Click here to Skip to main content
15,566,394 members
Please Sign up or sign in to vote.
0.00/5 (No votes)
See more:
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);
    }
}
Posted
Comments
PIEBALDconsult 18-Jun-21 19:54pm    
Might this help?
https://docs.microsoft.com/en-us/dotnet/api/system.data.dataview.totable?view=net-5.0#System_Data_DataView_ToTable_System_Boolean_System_String___
I gather it can copy selected columns in a specified order.

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)



CodeProject, 20 Bay Street, 11th Floor Toronto, Ontario, Canada M5J 2N8 +1 (416) 849-8900