How to use SqlXml in C# for Bulk Uploading.

     In this example, I was bulk uploading MS Access file on SQL Server.
Assume you have one MS Access file wish table name 'Employee'

using System;
using System.Data;
using System.Data.OleDb;
using System.Data.SqlClient;
using System.Data.SqlTypes;
using System.Configuration;
using Logic;
using System.IO;
using System.Xml;
using Logic.Data;
public partial class ExportData2 :  System.Web.UI.Page
{

    bool ErrorOccurs = false;
    string strAccessFilePath = "";
    string strErrorMsg = null;
    string strHTML = null;
    SqlTransaction sqlTran = null;
    SqlCommand SqlCmd = null;
    OleDbCommand objCmd = null;
    SqlConnection SqlConn = new SqlConnection(ConfigurationManager.ConnectionStrings["sqlCon"].ConnectionString);
    SqlConnection SqlDAConn = new SqlConnection(ConfigurationManager.ConnectionStrings["sqlCon"].ConnectionString);
    OleDbDataAdapter objAdapter = null;

    string strSql = null;
    private void Page_Load(System.Object sender, System.EventArgs e)
    {

    }

   // Create a button with name 'btnExport' and generate event. 
    protected void btnExport_Click(System.Object sender, System.EventArgs e)
    {
        UploadAccessFile();    
        fnSaveSched();
     
    }
   // This function upload your MS Access file to server so that it can read the file from there.
    private void UploadAccessFile()
    {
        if (string.IsNullOrEmpty(AccessFile.PostedFile.FileName))
        {
            lblerror.Text = "Please select an access file to upload.";
            //"Please select a valid excel file to upload."
            ErrorOccurs = true;
            return;
        }

        if (AccessFile.PostedFile.ContentLength != 0)
        {

            if ((AccessFile.PostedFile.ContentType == "application/msaccess") || (AccessFile.PostedFile.ContentType == "application/octet-stream"))
            {
            }
            else
            {
                lblerror.Text = "Please select an access file to upload.";
                //"Please select an access file to upload."
                ErrorOccurs = true;
                return;
            }
            string strFileNamePath = AccessFile.PostedFile.FileName;
            int intFileNameLength = strFileNamePath.LastIndexOf("\\");
            string strFileNameOnly = strFileNamePath.Substring(intFileNameLength + 1);

            intFileNameLength = strFileNameOnly.LastIndexOf(".");
            strFileNameOnly = strFileNameOnly.Substring(0, intFileNameLength);
            //strFileNameOnly += "_" + DateTime.Now.ToString("yyyyMMdd_HHmmss") + "(" + Session.SessionID + ").mdb";
            strFileNameOnly += "_" + DateTime.Now.ToString("yyyyMMdd_HHmmss") + ".mdb";

            strAccessFilePath = Server.MapPath("../Docs\\") + strFileNameOnly;
            AccessFile.PostedFile.SaveAs(strAccessFilePath);
        }
    }
  
 / /This function convert your DataTable  to XML file.
    private SqlXml fnXmlString(DataTable dt, string TableName)
    {
        using (StringWriter sw = new StringWriter())
        {
            dt.TableName = TableName;
            dt.WriteXml(sw);
            StringReader transactionXml = new StringReader(sw.ToString());
            XmlTextReader xmlReader = new XmlTextReader(transactionXml);
            SqlXml sqlXml = new SqlXml(xmlReader);
            //dtr.fnInsTemRegFromXML(pfr, sqlXml);
            return sqlXml;
        }
    }


/ /This function saves your XML file to MS SQL Server.
    private void fnSaveSched()
    {
        string ConnectionString = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" + strAccessFilePath + ";Persist Security Info=False;";
        OleDbConnection objConn = new OleDbConnection(ConnectionString);
        OleDbDataAdapter objAdapter = null;    
        DataTable tblAccessDataEmp = new DataTable();
        strSql = "SELECT * FROM Employee";
        objAdapter = new OleDbDataAdapter(strSql, objConn);
        objAdapter.Fill(tblAccessDataEmp);
        if (tblAccessDataEmp.Rows.Count > 0)
        {
            SqlXml sqlXml = fnXmlString(tblAccessDataEmp, "SCHED");
            int temp = this.fnInsSchedByXml(sqlXml);
            if (temp > 0)
            {
                string msg = "Uploaded Successfully";
            }
        }      
    }

   / /This function connects to SQL Server and call procedure for bulk uploading the records.
    public int fnInsSchedByXml(SqlXml SchedTable)
    {
        SqlCommand cmd = null;
        SqlConnection sqlCon = null;
        string sqlSTR = ConfigurationManager.ConnectionStrings["sqlCon"].ConnectionString;
        using(SqlConnection conn = new  SqlConnection())
        {
            conn.Open();
             cmd = new SqlCommand("p_InsSchedTableByXml",conn);
             cmd.CommandType = CommandType.StoredProcedure;
             cmd.Parameters.Add("@SchedTable", DbType.Xml).Value = SchedTable;
             int temp  = cmd.ExecuteNonQuery();
             return temp;
        }    
    }  
}


--Example of procedure that called by function
CREATE PROCEDURE p_InsSchedTableByXml (@EmpTable XML)
AS
BEGIN
INSERT INTO EMPLOYEE (
EmployeeID
,EName
,EmpDept
)
SELECT t.value('(EmployeeID/text())[1]', 'nvarchar(120)') AS EmployeeID
,t.value('(EName/text())[1]', 'nvarchar(120)') AS EName
,t.value('(EmpDept/text())[1]', 'nvarchar(120)') AS EmpDept
FROM @EmpTable.nodes('/DocumentElement/EMPLOYEE') AS TempTable(t)
END