Step 1: Create a table called Stores as follows (Execute the create table statement)
CREATE TABLE Stores(ItemIndex int,ItemName varchar(10),Price decimal)
Step 2: Create stored procedure sp_test_xml (execute following stored procedure)
Create proc sp_test_xml
(
@flag int = null,
@XMLInput text = null
)
as
begin
if(@flag = 1) --Flag
begin
declare @idoc int
exec sp_xml_preparedocument @idoc output,@XMLInput
--#te is temp table used to store the xml data.
----below statement for inserting xml data in temp table.
select ItemName,Price into #te from openxml(@idoc,'Root/Items',1)
with
(
ItemName varchar(10),
Price decimal
)
--cursor to read data from temp table
declare cu_xml cursor for select ItemName,Price from #te
declare @ItemName varchar(10),@Price decimal,@ItemIndex int
--Open cursor
open cu_xml
--fetch the temp table data row by row
fetch cu_xml into @ItemName,@Price
--while loop is used to check the end of cursor is reached or not
while (@@fetch_status = 0)
begin
--increment row index by one
select @ItemIndex = isnull(max(ItemIndex),0)+1 from Stores
--insert the values into Stores table.
insert into Stores values(@ItemIndex,@ItemName,@Price)
fetch cu_xml into @ItemName,@Price
end
--close the cursor and deallocate.
Close cu_xml
deallocate cu_xml
end
End
Step 3: Below procedure shows how data is sent to SQL Server in XML format.
Execute below query.
exec sp_test_xml @flag = 1,@XMLInput = '<Root>
<Items ItemName = "Rice" Price = "3.0"></Items>
<Items ItemName = "Rava" Price = "3.0"></Items>
</Root>'
Step 4:
SELECT * FROM Stores
while working with database usually asked question is how to send multiple rows of table data to SQL and how to insert it to a database.
Best way to this problem is to use of XML to send multiple rows of table data and insert it.In the Above procedure @XMLInput is the variable used to assign XML data.
Cursor is used to insert data row by row and the table column "ItemIndex" valued is incremented by one for every New row insertion to the table.