SqlBulkCopy - Importing XML data into SQL table

Question

I'm having proplems with importing an XML file using SqlBulkCopy. The XML file looks like this:

   <root>
    <Automobiles>
        <Car Id="1" Name="SomeModel1"/>
        <Car Id="2" Name="SomeModel2"/>
        <Car Id="3" Name="SomeModel2"/>
    </Automobiles>
   </root>

My table looks like this:

Int         Id
varchar    Name

Here is my code:

DataSet ds = new DataSet();
ds.ReadXml(Server.MapPath("autolist.xml"));

SqlConnection connection = new SqlConnection(
    ConfigurationManager.ConnectionStrings["Connection"].ToString());
SqlBulkCopy sbc = new SqlBulkCopy(connection);
sbc.DestinationTableName = "Automobiles";

foreach (DataColumn dc in ds.Tables[0].Columns)
{
    sbc.ColumnMappings.Add(dc.Caption, dc.Caption);
}

connection.Open();

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

connection.Close();

I could only import the Id but not Name because its attribute and the DataSet contains only one column. Is there any way to map the attributes to columns?

Maybe its better to use XmlDocument instead of DataSet?

I think i what is wrong. Dataset is quite different when i have some root element around Automobiles element and columns are missing in Dataset tables. It works well without root element.

Answer 1

I generally don't like using a DataSet to import XML data into my database; the DataSet class has a very specific way of structuring its data as XML and conversely, reading other XML.

That said, I prefer call the WriteToServer overload that takes an IDataReader implementation.

You can easily create an extension method that takes an IEnumerable<T> implementation and maps it to an IDataReader; basically, you map the properties of the class to the fields on the table by name and index where the index is also an index into an array of Expression instances (compiled to lambdas) that you create for T which takes the instance of T and returns the property value.

You'd generally map the IDataReader.Read implementation to IEnumerable<T>.MoveNext method.

Once you have something like this in place, you can map your XML to a lightwieght data object which mirrors the table and then pass the sequence of them into an extension method which maps the sequence to an IDataReader implementation.