SQL Bulk insert using xml

If we need to insert the data into a table from xml file then we have below options

     

    1)    By for loop in our business logic /code behind: this approach will time consuming in case of we have a big xml file.

    2)    By using Store procedure to insert bulk record into Database 

Assume we have this xml file

<?xml version="1.0" encoding="utf-8"?>
<Employees>
  <Employee >
    <Eid>11</Eid>
    <Name>John Smith</Name>
    <Designation>Team Leader</Designation>
    <Salary>50000</Salary>
    <DeptNo>1</DeptNo>
  </Employee>
  <Employee >
    <Eid>12</Eid>
    <Name>Sumit</Name>
    <Designation>Admin</Designation>
    <Salary>1000</Salary>
    <DeptNo>2</DeptNo>
  </Employee>
  <Employee >
    <Eid>13</Eid>
    <Name>John</Name>
    <Designation>Sr. Software Devoloper</Designation>
    <Salary>2000</Salary>
    <DeptNo>2</DeptNo>
  </Employee>
 </Employees>

Then the Store procedure to insert bulk record into Database will be

set ANSI_NULLS ON
set QUOTED_IDENTIFIER ON
GO
ALTER procedure [dbo].[SpEmpDetailsIns]
(@xmlstr ntext)
as
begin
  declare @hDoc int
  exec sp_xml_preparedocument @hDoc OUTPUT,@xmlstr
  insert into tbl_xml_Emp
    select xml.Eid,xml.name,xml.Designation,xml.Salary,xml.Deptno
    from OPENXML(@hDoc,'/Employees/Employee',2)
    with(Eid int,
         name varchar(50) 'Name',
         Designation varchar(50) ,
         Salary money,
         Deptno int 'DeptNo')xml
exec sp_xml_removedocument @hDoc
 
end 
 

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 same as before