Bulk insert into sql database using xml file

In this blog you will learn how to insert bulk into sql database using xml file.

Introduction : It is a common requirement in a website to have the ability to send an email to the organization via a contact form. Depending on the organization, they may need this information saved to a database or sent directly to a pre defined email account where this information will be processed. This article will illustrate the basics of retrieving the information from the contact page to an XML file, using the XML file to perform an insert into database table, and then transform the XML to the required format for sending to a specified account.

1. Create the table like
   
   create table tbl_xml_emp
   (Eno int not null,
   Ename varchar(50)not null,
   Designation varchar(50),
   salary money,
   Deptno int)
    
2. The real logic to saving this file is processed inside the SQL Stored Proc. There is a lot that is actually going on in this stored proc. First of all, we need to convert the text passed in the Incoming Parameter to an In Memory XML file. We achieve this by using the system stored Procedure sp_xml_preparedocument and pass it @xmlstr as an input, and @hDoc as an output which returns a reference to the XMLDocument.
   
   Write a Store procedure to insert bulk record into Database
   
   set ANSI_NULLS ON
   set QUOTED_IDENTIFIER ON
   GO
   ALTER procedure [dbo].[xmlToEmp]
   (@xmlstr ntext)
   as
   begin
     declare @hDoc int
     exec sp_xml_preparedocument @hDoc OUTPUT,@xmlstr
     insert into tbl_xml_Emp
       select xml.Eid,xml.Ename,xml.Designation,xml.Salary,xml.Deptno
       from OPENXML(@hDoc,'/Employees/Employee',2)
       with(Eid int,
            Ename varchar(50) 'Name',
            Designation varchar(50) ,
            Salary money,
            Deptno int 'DeptNo')xml
   exec sp_xml_removedocument @hDoc
    
   end 
    
3. You could pass this parameter through as Char, Varchar, nVarchar, or nText. It makes no difference other than limitation on the size of the document you wish to pass through
   Write the following code in button click event.
   
   string connStr = ConfigurationManager.ConnectionStrings["myConnectionString"].ConnectionString;
           DataSet ds = new DataSet();
           ds.ReadXml(Server.MapPath("XML_Files/Employee.xml"));
           string strxml = XDocument.Load(Server.MapPath("XML_Files/Employee.xml")).ToString();
           SqlConnection sqlconn = new SqlConnection(connStr);
           SqlCommand sqlcmd = new SqlCommand();
           sqlcmd.Connection = sqlconn;
           sqlcmd.CommandType = CommandType.StoredProcedure;
           sqlcmd.CommandText = "xmlToEmp";
           sqlcmd.Parameters.AddWithValue("@xmlstr", strxml);
           sqlconn.Open();
           sqlcmd.ExecuteNonQuery();
           sqlconn.Close();
    
4. XML File is
   
   <?xml version="1.0" encoding="utf-8"?>
   <Employees>
     <Employee >
       <Eid>1001</Eid>
       <Name>AAA</Name>
       <Designation>Software Devoloper</Designation>
       <Salary>20000</Salary>
       <DeptNo>10</DeptNo>
     </Employee>
     <Employee >
       <Eid>1002</Eid>
       <Name>BBB</Name>
       <Designation>Software Devoloper</Designation>
       <Salary>30000</Salary>
       <DeptNo>20</DeptNo>
     </Employee>
     <Employee >
       <Eid>1003</Eid>
       <Name>CCC</Name>
       <Designation>Software Devoloper</Designation>
       <Salary>20000</Salary>
       <DeptNo>10</DeptNo>
     </Employee>
    </Employees>

NOTE: Note: The problem is with the format of your XML, you have 2 solutions

1. Format your XML to look like
   
   <Employee >
   <Eid>1001</Eid>
   <Name>BBB</Name>
   <Designation>Software Devoloper</Designation>
   <Salary>30000</Salary>
   <DeptNo>20</DeptNo>
   </Employee>
   
   did you notice how <Employee> element have the column as children and each column value is a node text not an attribute.
   
   Then you will need to use "2" in the OPENXML function call
   
   from OPENXML(@hDoc,'/Employees/Employee',2)
    
2. <Employee Eid="1001" Name="BBB" Designation="Software Devoloper" Salary="30000" DeptNo="20"> </Employee>
   
   No change required for the OPENXML function call it remains 1)