Bulk Insert From XML To Data Table

Bulk Insert From XML To Data Table using Asp.net & C#.

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.