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.
    1. DataTable dtUsers; //which contains all users in rows  
    2. DataSet dsUsers = new DataSet();  
    3. dsUsers.Tables.Add(dtUsers);  
    4. string strUsersXML = ds.GetXml(); //you will get a string like following,   
    5.   
    6. strUsersXML = '<ROOT><student> < id > 1 < /id> < name > Prashant < /name> < age > 32 < /age> < /student> < student >  
    7.     < id > 2 < /id> < name > Swami < /name> < age > 42 < /age> < /student> < student >  
    8.     < id > 3 < /id> < name > Ash < /name> < age > 23 < /age> < /student> < student >  
    9.     < id > 4 < /id> < name > Kris < /name> < age > 12 < /age> < /student> < student >  
    10.     < 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.
    1. CREATE PROCEDURE InsertUsers(@doc nvarchar(max))  
    2. as  
    3. BEGIN  
    4. DECLARE @idoc int  
    5.   
    6. --Create an internal representation of the XML document.  
    7. EXEC sp_xml_preparedocument @idoc  
    8. OUTPUT, @doc  
    9. --Execute a SELECT statement that uses the OPENXML rowset provider.  
    10. Insert into Students SELECT id, name, age from  
    11. OPENXML(@idoc, '/ROOT/student', 2)  
    12. WITH(id int, name varchar(50), age int)  
    13.     --Select * from @Students  
    14. END  
    Enjoy.
Ebook Download
View all
Learn
View all