Post
Ask Question

Cursor and XML Strore Procedure in SQL

Introduction

In this article, we will learn about using cursors and XML-stored procedures in SQL. XML stored procedures allow you to input, output, and manipulate XML data in a database. 

Step 1. Create a table called Stores as follows (Execute the SQL CREATE TABLE statement)

CREATE TABLE Stores
(
    ItemIndex int,
    ItemName varchar(10),
    Price decimal
);

The above SQL statement creates a table called "Stores." This table has three columns: "ItemIndex," "ItemName," and "Price." "ItemIndex" is an integer, "ItemName" is a character string of length 10, and "Price" is a decimal number. The output of the above SQL statement is shown below FIgure-1.

Step 2. Create stored procedure sp_test_xml (execute the SQL CREATE PROCEDURE statement). If you want to learn How To Create a Stored Procedure In SQL Server, read more on How To Create a Stored Procedure In SQL Server.

CREATE PROCEDURE sp_test_xml (@flag INT = NULL, @XMLInput TEXT = NULL)
AS
BEGIN
  IF (@flag = 1) 
  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 = COALESCE(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;

The above SQL statement creates a stored procedure named "sp_test_xml" with two parameters: "flag" and "XMLInput." The stored procedure takes an XML string as input and performs some actions based on the value of "flag." If "flag" is 1, the stored procedure will parse the XML string, insert the data into a temporary table, read the data from the temporary table row by row using a cursor, and insert the data into a permanent table named "Stores." The stored procedure then closes the cursor and frees up the memory. The output of the above SQL statement is shown below in Figure 2.

If you want to learn why we use stored procedures, read more on Why We Use Stored Procedure In SQL Server.

Step 3. The below procedure shows how data is sent to SQL Server in XML format. (Execute the 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>'

The above SQL statement executes a stored procedure named "sp_test_xml." It has two input parameters, "@flag" set to 1, and "@XMLInput," which contains XML data as a string. The XML data has a root element, "Root," with two child elements, "Items," with attributes "ItemName" and "Price." The output of the above SQL statement is shown below FIgure-3.

Step 4. Fetches are all stored in a database table.

SELECT * FROM Stores;

The above SQL statement retrieves all columns and all rows from a table named "Stores." The output of the above SQL statement is shown below FIgure-4.

While working with a database, the usually asked question is how to send multiple rows of table data to SQL and how to insert it into a database. The best way to this problem is to use XML to send multiple rows of table data and insert it. In the above procedure, @XMLInput is the variable used to assign XML data.

The cursor is used to insert data row by row, and the table column "ItemIndex" value is incremented by one for every New row insertion to the table.

Conclusion

Here, we have learned to use cursors and XML-stored procedures in SQL.

If you require any clarification/suggestions on the article, please leave your questions and thoughts in the comment section below. Follow C# Corner to learn more new and amazing things about SQL Server or to explore more technologies.

Thanks for reading, and I hope you like it.


Similar Articles