There are some cases when a programmer need a way to bulk insert data into sql. There are lot many option available to achieve this task. In this post we are going with a XML. We will look into how can we do bulk insert using XML,ASP.NET,SQL

So to bulk insert using XML, we first need to generate XML from coding and pass that XML to the database. So in the below code we are converting our data table to xml.

StringWriter swStringWriter = new StringWriter();

string strXML = “”;
(dtTemp).WriteXml(swStringWriter);
strXML = swStringWriter.ToString();

Now we have one XML which we need to process in SQL Server to convert it into the data tables.

Suppose we have a below XML which we want to convert to data tables.

<Employees>
	<Employee>
		<EmployeeDetails>
			<Id>1</Id>
			<FirstName>John</FirstName>
			<LastName>Smith</LastName>
			<PhoneNumber>123123123</PhoneNumber>
			<Email>johnsmith@gmail.com</Email>
			<Address>123 Main Street</Address>
		</EmployeeDetails>
		<Salary>
			<BasicSalary>5000</BasicSalary>
			<Incentives>450</Incentives>
			<Bonus>5000</Bonus>
			<VehicleAllowance>150</VehicleAllowance>
		</Salary>
		<Benefits>
			<ProvidentFund>650</ProvidentFund>
			<Mediclaim>1200</Mediclaim>
			<LifeInsurance>3500</LifeInsurance>
		</Benefits>
	</Employee>
	<Employee>
		<EmployeeDetails>
			<Id>2</Id>
			<FirstName>Alexa</FirstName>
			<LastName>David</LastName>
			<PhoneNumber>789789789</PhoneNumber>
			<Email>alexa@live.com</Email>
			<Address>54 Wall Street</Address>
		</EmployeeDetails>
		<Salary>
			<BasicSalary>6500</BasicSalary>
			<Incentives>500</Incentives>
			<Bonus>7000</Bonus>
			<VehicleAllowance>300</VehicleAllowance>
		</Salary>
		<Benefits>
			<ProvidentFund>650</ProvidentFund>
			<Mediclaim>1500</Mediclaim>
			<LifeInsurance>5000</LifeInsurance>
		</Benefits>
	</Employee>
</Employees>

Now below is the simple sql query which will give me three data tables each for EmployeeDetails,Salary and Benefits.

DECLARE @XMLDATA XML

SET @XMLDATA = N'
<Employees>
	<Employee>
		<EmployeeDetails>
			<Id>1</Id>
			<FirstName>John</FirstName>
			<LastName>Smith</LastName>
			<PhoneNumber>123123123</PhoneNumber>
			<Email>johnsmith@gmail.com</Email>
			<Address>123 Main Street</Address>
		</EmployeeDetails>
		<Salary>
			<BasicSalary>5000</BasicSalary>
			<Incentives>450</Incentives>
			<Bonus>5000</Bonus>
			<VehicleAllowance>150</VehicleAllowance>
		</Salary>
		<Benefits>
			<ProvidentFund>650</ProvidentFund>
			<Mediclaim>1200</Mediclaim>
			<LifeInsurance>3500</LifeInsurance>
		</Benefits>
	</Employee>
	<Employee>
		<EmployeeDetails>
			<Id>2</Id>
			<FirstName>Alexa</FirstName>
			<LastName>David</LastName>
			<PhoneNumber>789789789</PhoneNumber>
			<Email>alexa@live.com</Email>
			<Address>54 Wall Street</Address>
		</EmployeeDetails>
		<Salary>
			<BasicSalary>6500</BasicSalary>
			<Incentives>500</Incentives>
			<Bonus>7000</Bonus>
			<VehicleAllowance>300</VehicleAllowance>
		</Salary>
		<Benefits>
			<ProvidentFund>650</ProvidentFund>
			<Mediclaim>1500</Mediclaim>
			<LifeInsurance>5000</LifeInsurance>
		</Benefits>
	</Employee>
</Employees>'

SELECT E.Emp.value('(Id)[1]','INT') AS Id,
	   E.Emp.value('(FirstName)[1]','VARCHAR(50)') AS FirstName,
	   E.Emp.value('(LastName)[1]','VARCHAR(50)') AS LastName,
	   E.Emp.value('(PhoneNumber)[1]','VARCHAR(50)') AS PhoneNumber,
	   E.Emp.value('(Email)[1]','VARCHAR(50)') AS Email,
	   E.Emp.value('(Address)[1]','VARCHAR(200)') AS Address
	   FROM @XMLDATA.nodes('Employees/Employee/EmployeeDetails') as E(Emp)
	   
SELECT E.Emp.value('(../EmployeeDetails/Id)[1]','INT') AS Id,
	   E.Emp.value('(BasicSalary)[1]','VARCHAR(50)') AS BasicSalary,
	   E.Emp.value('(Incentives)[1]','VARCHAR(50)') AS Incentives,
	   E.Emp.value('(Bonus)[1]','VARCHAR(50)') AS Bonus,
	   E.Emp.value('(VehicleAllowance)[1]','VARCHAR(50)') AS VehicleAllowance	   
	   FROM @XMLDATA.nodes('Employees/Employee/Salary') as E(Emp)

SELECT E.Emp.value('(../EmployeeDetails/Id)[1]','INT') AS Id,
	   E.Emp.value('(ProvidentFund)[1]','VARCHAR(50)') AS ProvidentFund,
	   E.Emp.value('(Mediclaim)[1]','VARCHAR(50)') AS Mediclaim,
	   E.Emp.value('(LifeInsurance)[1]','VARCHAR(50)') AS LifeInsurance   
	   FROM @XMLDATA.nodes('Employees/Employee/Benefits') as E(Emp)

The output of above query is as below.