Bulk Insert From XML To Data Table

If you want to insert large number of data in table with just one shot then you can do it.
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.

1. I need to have a data table or data set which contains the whole data to be inserted in to DB.
   

   DataTable dtUsers; //which contains all users in rows
   DataSet dsUsers = new DataSet();
   dsUsers.Tables.Add(dtUsers);
   string strUsersXML = ds.GetXml(); //you will get a string like following,
   
   strUsersXML = '<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>'

   Below is the example: Open Query analyzer and paste the below code.
   
   
2. Declared the XML Variable first and set the value to it.
   

   CREATE PROCEDURE InsertUsers(@doc nvarchar(max))
   as
   BEGIN
   DECLARE @idoc int
   
   --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
   END

   Enjoy.