SQL Bulk Copy from DataTable using XML

To copy data from DataTable or DataSet to SQL Server, we need to use either foreach or for loop. It is OK when the count of rows is reasonable. But if the data is like 1 lakh or 1 crore, then it is not possible to round the loop for that much time. Then what do we do? A simple and easy way to follow is use XML. I am sure you all are more or less aware of this XML. It's like the database with tags. Or I better to say it's a database. It keeps data into a file with extension of ".xml".

Our intention is to convert the DataTable's data into an XML file and send it to server, where with the help of a stored procedure, we will extract data and insert into the database table. So let's come and see how to do this...

First of all, create a new database and create a new table, name it as you want. Database table structure will be like this:

Name NVarChar(255) not null
Address NVarChar(255) not null
Phone NvarChar(12) not null

OK, now we need to create a new DataTable and put some data on it. Make sure your column and the database table's column are the same.

DataTable dt = new DataTable();
dt.Columns.Add("Name");
dt.Columns.Add("Address");
dt.Columns.Add("Phone");

dt.Rows.Add("Arkadeep", "Kolkata", "123456890");
dt.Rows.Add("Saikat", "Chennai", "99999999");
 
dt.Rows.Add("Sucheta", "Delhi", "9876543210");

Now you have to convert this DataTable into XML. To do this, copy and paste the following code after the DataTable section.

private static string ConvertToXML(DataTable dt)
{
      DataSet dsBuildSQL = new DataSet();
      StringBuilder sbSQL;
      StringWriter swSQL;
      string XMLformat;
      try
      {
           sbSQL = new StringBuilder();
           swSQL = new StringWriter(sbSQL);
           dsBuildSQL.Merge(dt, true, MissingSchemaAction.AddWithKey);
           dsBuildSQL.Tables[0].TableName = "DataTable";
           foreach (DataColumn col in dsBuildSQL.Tables[0].Columns)
           {
               col.ColumnMapping = MappingType.Attribute;
           }
           dsBuildSQL.WriteXml(swSQL, XmlWriteMode.WriteSchema);
           XMLformat = sbSQL.ToString();
           return XMLformat;
       }
       catch (Exception sysException)
       {
           throw sysException;
       }
}

Call this method to convert the DataTable to XML.

String xmlData = ConvertToXML(dt);

Now pass the value to the stored procedure like the following way:

SqlConnection conn = new SqlConnection(WebConfigurationManager.ConnectionStrings["connection"].ToString());
SqlCommand command = new SqlCommand("sp_InsertData '" + xmlData + "'", conn);
conn.Open();
command.ExecuteNonQuery();

conn.Close();

Now let's check the stored procedure sp_InsertData.

CREATE PROCEDURE sp_InsertData
(@xmlString VARCHAR(MAX))
AS
BEGIN

      DECLARE @xmlHandle INT

      DECLARE @stagingTable TABLE
      (
         [Name]               VARCHAR(50),
         [Address]            VARCHAR(50),
         [Phone]              VARCHAR(50)
      )
               
      EXEC sp_xml_preparedocument @xmlHandle output, @xmlString  

      INSERT INTO@stagingTable 
      SELECT  [Name]    ,
                  [Address],
                  [Phone]     
      FROM  OPENXML (@xmlHandle, '/DataTable',1)
                        WITH ([Name]            varchar(50)       '@Name',
                                [Address]       varchar(50)       '@Address',
                                [Phone]         varchar(50)       '@Phone'
                               )

      INSERT INTOSampleData ([Name], [Address], [Phone])
            (SELECT [Name] , [Address],[Phone]FROM @stagingTable)
      
      EXEC sp_xml_removedocument @xmlHandle
END

Now run your project and after running this, check your database table to see whether data has been inserted or not...