Post
Ask Question

Bulk insert 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)