Bulk Insert From XML To Table

If you want to insert large number of data in table with just one shot then you can do it by this article. While doing my project I came to scenario where I want to send the large number to data to SQL and then insert into tables. I found this very useful. What I did is I passed all records in form of XML in SQL Procedure.

Below is the example: Open Query analyzer and paste the below code

1) Declared the XML Variable first and set the value to it.

DECLARE @idoc int




DECLARE @doc varchar(8000)

SET @doc ='<ROOT><student>

<id>1</id>

<name>Prashant</name>

<age>32</age>

</student>

<student>

<id>2</id>

<name>Swami</name>

<age>42</age>

</student>

<student>

<id>3</id>

<name>Ash</name>

<age>23</age>

</student>

<student>

<id>4</id>

<name>Kris</name>

<age>12</age>

</student>

<student>

<id>5</id>

<name>Derek</name>

<age>75</age>

</student>

</ROOT>'

2) Now Include this:

--Create an internal representation of the XML document.

EXEC sp_xml_preparedocument @idoc

OUTPUT, @doc

-- Execute a SELECT statement that uses the OPENXML rowset provider.

Insert into Students SELECT id, name, age from 

OPENXML (@idoc, '/ROOT/student',2) 

WITH
(id  int, name varchar(50), age int)




Select * from
@Students

Enjoy!!!!!!