What is the best way to insert Bulk Xml (XElement) data to Sql server Table using C#?

Question

I have a XElement Object, I need to insert this into Sql Server Table using C#. (the XML file contains more than 2 lakhs records)

Can you help me in this? OR else any alternate?

you should use XmlWriter if the data is huge in an xml file rather than loading it in memory at once. — Samarsh, Jan 10 '14 at 12:45
you can see this [link](http://stackoverflow.com/questions/20474672/put-xelement-into-database), may be helful. — Amir, Jan 10 '14 at 13:05

MSanika · Accepted Answer · 2014-01-16T13:41:38.973

XElement output = XElement.Load("c:\\temp\\input.xml");
IEnumerable<XElement> users = output.Elements(); 

        DataTable dt = new DataTable();
                dt.Columns.Add("CLIENT_INPUT_MHS_ID", typeof(int));
                dt.Columns.Add("CLIENT_INPUT_MHS_GUID",typeof(Guid));
                dt.Columns.Add("ITEM", typeof(string));
                dt.Columns.Add("ITEM_ID", typeof(int));
                dt.Columns.Add("ITEM_NUMBER", typeof(string));
                dt.Columns.Add("CATEGORY", typeof(string));        

        foreach (XElement str in users)
        {
            DataRow dr = dt.NewRow();
            foreach (XElement node in str.Elements())
            {
                dr[node.Name.LocalName] = node.Value;
            }

            dt.Rows.Add(dr);
        }
SqlBulkCopy bulkCopy = new SqlBulkCopy("ConnectionString...");
 using (bulkCopy )
                {
                    bulkCopy .BulkCopyTimeout = 0;
                    bulkCopy .ColumnMappings.Add(dt.Columns[0].ColumnName, "CLIENT_INPUT_MHS_ID");
                    bulkCopy .ColumnMappings.Add(dt.Columns[1].ColumnName, "CLIENT_INPUT_MHS_GUID");
                    bulkCopy .ColumnMappings.Add(dt.Columns[2].ColumnName, "ITEM");
                    bulkCopy .ColumnMappings.Add(dt.Columns[3].ColumnName, "ITEM_ID");
                    bulkCopy .ColumnMappings.Add(dt.Columns[4].ColumnName, "ITEM_NUMBER");
                    bulkCopy .ColumnMappings.Add(dt.Columns[5].ColumnName, "CATEGORY");
                    bulkCopy.DestinationTableName = "DestinationTableName";                   
                    bulkCopy.WriteToServer(dt); }



Its working fine with my scenario.

score 1 · Answer 2 · edited Jan 10 '14 at 14:43

1

I would suggest, first load data from XML, then DataSet and then go for SQL Bulk Copy. Things would be easy then onward.

Loading XML data to DataSet can be done as below:

DataSet ds = new DataSet();
ds.ReadXml("xml file path");

edited Jan 10 '14 at 14:43

Amicable

3,115
3
49
77

answered Jan 10 '14 at 14:00

M.K

218
3
10

What is the best way to insert Bulk Xml (XElement) data to Sql server Table using C#?

2 Answers2