Cursor and XML Strore Procedure in SQL

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.