Importing XML to SQL table

This post demonstrate importing the data expressed in XML to Microsoft SQL Server using the SqlBulkCopy class.

Before importing, you should create a table to receive the data that the SqlBulkCopy class process. Here in this example we will use a table “DummyEmails” to receive the data. The XML file which we use to import is “Email.xml”.

try
{
DataSet xmlDS = new DataSet();
xmlDS.ReadXml(Server.MapPath("Email.xml")); //Name of the XML file

//Create the connection object
SqlConnection connection = new SqlConnection(ConfigurationManager.ConnectionStrings[ "LMSConnectionString"].ToString());


//Creating the SqlBulkCopy object
SqlBulkCopy sqlBC = new SqlBulkCopy(connection);


//Specify the destination database table name
sqlBC.DestinationTableName = "DummyEmails";

//If the destination database table's column names doesn't match with the XML element names,
//then we have to relate the XML elements and the table column name.
//For that you can use the below code;
//sqlBC.ColumnMappings.Add("EmailId", "Email_id");
//sqlBC.ColumnMappings.Add("EmailAddress", "Email_Address");
//1st Parameter:Source Column, 2nd Parameter:Destination Column
//You may use the Column index too

connection.Open();
sqlBC.WriteToServer(xmlDS.Tables[0]);

connection.Close();
}
catch { }

The SqlBulkCopy class can be used to write data only to SQL Server tables. However, the data source is not limited to XML file; any data source can be used, as long as the data can be loaded to a DataTable instance or read with a IDataReader instance.