Loading xml data into sql server table using sqlbulkcopy

We want to load the following XML data into SQL Server database.

Notice that we have Departments and Employees data in the XML file. We would like to load Employees data into Employees table and Departments data into Departments table. After the data is loaded, the database tables should look as shown below.

The following are the steps to achieve this using SqlBulkCopy class

Step 1 : Create the database tables using the following sql script

   Create Database Test
   Go
   Use Test
   Go
   CREATE TABLE [dbo].[Department] (
    [Id]   INT          IDENTITY (1, 1) NOT NULL,
    [Name] VARCHAR (50) NULL,
    CONSTRAINT [PK_Department] PRIMARY KEY CLUSTERED ([Id] ASC)
   );
   CREATE TABLE [dbo].[Employee] (
    [Id]           INT          IDENTITY (1, 1) NOT NULL,
    [Name]         VARCHAR (50) NULL,
    [Salary]       INT          NULL,
    [DepartmentId] INT          NULL,
    CONSTRAINT [PK_Employee] PRIMARY KEY CLUSTERED ([Id] ASC),
    CONSTRAINT [FK_Employee_Department] FOREIGN KEY ([DepartmentId]) REFERENCES [dbo].[Department] ([Id])
   );

Step 3 : Create a new empty asp.net web application project. Name it DemoForSqlBulkCopy

Step 3 : Add a new xml file to the project. Name it Data.xml. Copy and paste the following XML.

   <Data>
	<Department Id="1" Name="HR" />
	<Department Id="2" Name="Development" />
	<Department Id="3" Name="Database" />
	<Department Id="4" Name="Business" />
  <Employee Id="1" Name="sachin" Salary="100000" DepartmentId="1" />
  <Employee Id="2" Name="arjun" Salary="23000" DepartmentId="1" />
  <Employee Id="3" Name="vikash" Salary="30000" DepartmentId="2" />
  <Employee Id="4" Name="pocha" Salary="43000" DepartmentId="3" />
  <Employee Id="5" Name="Nivedita" Salary="24000" DepartmentId="4" />
  <Employee Id="6" Name="agla" Salary="1000" DepartmentId="2" />
  <Employee Id="7" Name="dadad" Salary="10005" DepartmentId="1" />
 </Data>

Step 4 : Include the database connection string in web.config file

  <connectionStrings>
  <add name="TestConnectionString" connectionString="Data Source=SACHIN-PC\SQLEXPRESS;Initial Catalog=Test;Integrated Security=True" 
   providerName="System.Data.SqlClient" />
  </connectionStrings>

Step 5 : Add a new WebForm to the project. Drag and drop a button control on the webform. Double click the button control to generate the click event handler. Copy and paste the following code in the the click event handler method.

   protected void Button1_Click(object sender, EventArgs e)
        {
            string cs = ConfigurationManager.ConnectionStrings["TestConnectionString"].ConnectionString;
            using (SqlConnection con = new SqlConnection(cs))
            {
                DataSet ds = new DataSet();
                ds.ReadXml(Server.MapPath("~/Data.xml"));
                DataTable dtDept = ds.Tables["Department"];
                DataTable dtEmp = ds.Tables["Employee"];
                con.Open();
                using (SqlBulkCopy bc = new SqlBulkCopy(con))
                {
                    bc.DestinationTableName = "Department";
                    bc.ColumnMappings.Add("Id", "Id");
                    bc.ColumnMappings.Add("Name", "Name");             
                    bc.WriteToServer(dtDept);
                }

                using (SqlBulkCopy bc = new SqlBulkCopy(con))
                {
                    bc.DestinationTableName = "Employee";
                    bc.ColumnMappings.Add("Id", "Id");
                    bc.ColumnMappings.Add("Name", "Name");
                    bc.ColumnMappings.Add("Salary", "Salary");
                    bc.ColumnMappings.Add("DepartmentId", "DepartmentId");
                    bc.WriteToServer(dtEmp);
                }
            }
        }