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.

I've tried importing your code and the DateSet has 2 columns (Id and Name) so that can't be the problem. Could something go wrong with your mapping? — Wouter de Kort
– Wouter de Kort ♦, Commented Oct 27, 2011 at 18:06

casperOne · Accepted Answer · 2011-10-28 17:12:29Z

3

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.

edited Oct 28, 2011 at 17:12

answered Oct 27, 2011 at 18:12

casperOne

74.7k19 gold badges189 silver badges262 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

ilkin Over a year ago

Thanks for your answer. I did some resarch after your answer and found som examples.

Collectives™ on Stack Overflow

SqlBulkCopy - Importing XML data into SQL table

1 Answer 1

1 Comment

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Linked

Related