AKA Marketing.com Logo            VISIT THE BLOG            

Blogged thoughts, is our web blog. Expect views, opinion, rants and tirades about everything and anything 

« Home / Forums »        

 


Subscribe to our SEO / IT related blog by entering your email address below

Blogged thoughts

| by the www.akamarketing.com team



Importing XML data into a SQL Server table with C# asp.net

I’d rather not store the XML files Im using in my current project onto the server as they are very verbose so I decided to use the SQLBulkCopy class in .net to allow me to write the data (and only the data - no schema related stuff) within the XML files to a SQL Server database table which gives me the power of SQL (for data filtering) which I’m more familiar with than XPath. The process for doing this is extremely easy, check out the code snippet below:

DataSet reportData = new DataSet();
reportData.ReadXml(Server.MapPath(”report.xml”));

SqlConnection connection = new SqlConnection(”CONNECTION STRING”);
SqlBulkCopy sbc = new SqlBulkCopy(connection);
sbc.DestinationTableName = “report_table”;
       
//if your DB col names don’t match your XML element names 100%
//then relate the source XML elements (1st param) with the destination DB cols
sbc.ColumnMappings.Add(”campaign”, “campaign_id”);
sbc.ColumnMappings.Add(”cost”, “cost_USD”);

connection.Open();

//table 4 is the main table in this dataset
sbc.WriteToServer(reportData.Tables[4]);

connection.Close();

//remove the xml file

Basically your creating a DataSet, populating it with XML data from a *.xml file somewhere on your system and passing the DataTable of interest from your DataSet into the WriteToServer method of the SQLBulkCopy class. The table specified in DestinationTableName must exist, additionally if your DB column names do not match your XML element names 100% you will need to use the ColumnMappings property to let SQLBulkCopy know which XML elements match to which DB cols. In the code above I’m telling SQLBulkCopy to pair the ‘campaign_id’ and ‘cost_USD’ columns in the DB with the ‘campaign’ and ‘cost’ elements from the XML file. XML elements which are not explicitly specified will not be written to the DB table.

The SQLBulkCopy class is available in the System.Data.SqlClient namespace, so you will need to add a using statement at the top of your code if you want to instantiate a SQLBulkCopy instance. Although my project will only really involve XML data with a max of about 2,000 elements I’ve done a bit of reading about some of the performance benefits some .net developers are getting by using SQLBulkCopy, a couple of people said they were writing 40-50K XML elements (how big were the elements though?) to their DB in only a few seconds, now that’s quick… it sure beats looping over ‘insert into’ statements.

Resources:
http://dotnetslackers.com/Articles/ADO_NET/SqlBulkCopy_in_ADO_NET_2_0.aspx
http://www.sqlteam.com/article/use-sqlbulkcopy-to-quickly-load-data-from-your-client-to-sql-server

31 Comments on “Importing XML data into a SQL Server table with C# asp.net”
1| David Callan said,

Just inserted around 50,000 records in four seconds…

2| Lokesh said,

It is giving an error: Login failed for user ‘XYZ’

3| Philip said,

Thanks so much , gave exactly what i required

4| Zar Ni Mg said,

Thank you this is very usefull for xml developer

with best regards

5| jr_bau said,

Sorry, I’m a newbie here…
Got this error

Cannot find table 4.

Line 50: sbc.WriteToServer(reportData.Tables[4]);

I don’t get that line, how can I specify table there? Thanks in advance.

6| jr_bau said,

Got this by changing tables to 0. Thanks a lot, been googling for this solution for a long time!

7| decode said,

Hello.. Sorry but i dont get this code: sbc.WriteToServer(reportData.Tables[4]);
What is 4 means? Is that for the number of fields in the source data? ….. thanks for the help..

8| David Callan said,

The writeToServer method takes a datatable as a parameter. reportData in this instance is a dataset and Tables[4] is the 5th table in the dataset. It’s indexed based.

9| decode said,

thanks for the response! I got the code already. But is there a possibility to transfer multiple data from an excel xml format file to sql server using C#?.. I really had a hard time on this too.
Thanks again for the help..

10| shrikant said,

how can i lock a particular control from outside that form in which the control is made.
thanx

11| shrikant said,

Hi all

Can somebody tell me a easy way for the following:
- I have a DataSet in C# (with four table)
- I have SQL Server (2005)
Now I would like to store the data in the DataSet into a SQL Server
Table. I need to do this in ASP.net using C#.
Dataset and SQL tables,both have same fields.
Would be very happy for any comments,
thanks in advance.

Best regards
Shrikant Dhyani

12| Boh said,

Very useful function to insert large amount of data into database table, we were using for loop to call insert stored procedure, it takes 10 minutes to insert 50k records, now by using this method it only takes 8 seconds

Thank you!

Also for someone want to export large amount data from dataset to CSV file, I found the best way is to use dataset.writetoxml function and transform the xml to csv with xslt.

13| Ali said,

when i inser record using SqlBulkCopy. it gives an error.

The given value of type String from the data source cannot be converted to type uniqueidentifier of the specified target column.

InnerException = {”Failed to convert parameter value from a String to a Guid.”}

Data is comming from C# and trying to save in sql server.

/Thanks

14| Ali said,

I read xml document and add records in dataset. I am using SqlBulkCopy.
When i try to insert record into sql database it gives error on this line:- sbc.WriteToServer(ds.Tables[0]);

Error is: The given value of type String from the data source cannot be converted to type uniqueidentifier of the specified target column
InnerException = {”Failed to convert parameter value from a String to a Guid.”}

I dont know how to convert string to guid when i am inserting record using SqlBulkCopy?

Can any body help me plz?

/Thanks

15| cutie said,

I need to insert data from two different tables of the dataset to the database. That is both ds.Tables[0] and ds.Tables[1] to a single table in the database.

Please tell me how do I do that. Urgent.

16| Mudit said,

THANKS A TON!!

A tip for Cutie: Import the table into a temporary table using above method and later do rest of the things in a stored procedure.

17| Abhishek said,

Nice Tips for the Beginners,
I think .net framework has made it quiet easy to use XML via dataset.

18| Remya said,

Hi I am getting an exception ”Cannot find tables in reportdata.Tables[0]” I am reading and loading a xml file. Why is this slo

19| Rahul said,

That is excellent . I made a change by keeping the table[0], and everything works fine.

Thanks

20| Ankit said,

Hi,I want to store XML file as it is in the Database.I am using Sql server and Asp.Net…

21| Milan said,

Would this method not copy data if in case data already exists in the database?

22| Sreelakshmi said,

This function inserts parameter name and value into the table..

But i want to insert value acording to the database parameter name wise..

23| Munazzah Safwan said,

thank you so much for this article .you did this xml to sql transfer of data in a very easy way.this article helped me alot

24| Stephen said,

Awesome. Very handy. Worked at first try.

25| BBforever said,

Friends,
Help me on this.
I got a error as “Cannot find table 0.” TableName is Product.
This is the code.

ds = new DataSet();
XmlDocument xmldoc = new XmlDocument();
xmldoc .Load (@”C:\demo.xml”);
SqlBulkCopy sbc = new SqlBulkCopy(con );
sbc.DestinationTableName = “product”;
sbc.ColumnMappings.Add(”pname”, “productname”);
sbc.ColumnMappings.Add(”price”, “productprice”);
sbc.WriteToServer(ds.Tables[0]);

Thanks in advance.
BB

26| Manish said,

Awesome. Very handy. Worked at first try. Thanks

27| Imran said,

Said, Excellent simple and compact code. Thnaks

28| Mark said,

That;s what i needed! I used to create objects and fill these from out a xmltextreader. After that i did the inserts through stored procedures. For XML documents that were 500-600MB each it took quite some time, though it was robust in processing. It took about 4 hours to process it.

This way, as desribed here takes only 1 minute 25 seconds…… exactly what i was looking for!

29| praba said,

i got the error..HTTP exception unhandled by user code.. plz give explain deeply…

30| Ryan said,

Poorly placed google ads, but thanks for the example, works excellently

31| Nishant said,

Perfect

Leave a Comment
Name:
Email:
Website:
 
HOME | ABOUT US | CONTACT US | SITEMAP | GOOGLE SITE SEARCH | TOP
12 Lorcan Crescent, Santry, Dublin 9, Ireland +353 87 9807629