Sending multiple rows to the Database from an Application in XML Format

If the XML data is in the form 

then the pass the  xml data to the stored procedure and the procedure will parse the xml and inserts the data into the database as written below in the stored procedure.


Create a table called Employee Having the following fields.
employeeId(int),Name(varchar(50)),salary(money),
designation(varchar(50)),department(varchar(50)) and 


write the procedure as below.



Create Procedure InsertData
(
@xml XML
)
as
BEGIN
Insert INTO Employee(employeeId,Name,salary,designation,department)
SELECT T.Item.value('@EmployeeId', 'Int') EmployeeId,   T.Item.value('@Name', 'VARCHAR(50)') Name,   T.Item.value('@salary', 'Money') salary,   T.Item.value('@designation', 'VARCHAR(50)') designation,   T.Item.value('@department', 'VArchar(50)') department  
 FROM @xml.nodes('/ROWS/ROW') AS T(Item)
END

This will parse the XML Data passed and will insert the values into the database.


Please refer to the following link.





http://www.sqlservercentral.com/articles/T-SQL/67603/