Sunday 13 January 2013

Bulk insert into Sql Server table with XML input


Let's say I have a table named User

Create stored procedure "addtodb".
-------------------------------------
Create Procedure addtodb @exml xml
as
begin
Insert into [student].[dbo].[User](UserId,UserName)
Select p.value('@UserId','integer')as Id,p.value('@UserName','varchar(50)')as Name
from @exml.nodes('/employeeData/employee') as abc(p)
end
--------------------------------------

Now execute it :
------------------
Exec addtodb @exml = '<employeeData>
                  <employee UserId="1" UserName="Rsu"/>
                  <employee UserId="2" UserName="PSU"/>
                  <employee UserId="3" UserName="Nsu"/>
                  <employee UserId="4" UserName="Ksu"/>
                  <employee UserId="5" UserName="Dsu"/>
               </employeeData>'



No comments:

Post a Comment