Jim Ver Jim Ver - 6 months ago 16
SQL Question

How can I create a cursor from xml nodes in a stored procedure in SQL Server?

I'm passing an XML document to my stored procedure as an argument. Then I'm trying to fill a cursor in order to loop through the elements of the XML. My question is how can I select every element of this XML Document and fill my cursor with them?

XML Document

<Authors>
<Author_id>1</Author_id>
<Author_id>2</Author_id>
</Authors>


Stored Procedure

CREATE PROCEDURE Insert_Publication
@authors xml
AS

DECLARE @id int

DECLARE authors_cursor CURSOR FOR
SELECT @authors.query('(/Authors/Author_id)')

open authors_cursor

FETCH NEXT FROM authors_cursor INTO @id

Answer

You can use .nodes() and .value():

DECLARE @authors XML = 
'<Authors>
    <Author_id>1</Author_id>
    <Author_id>2</Author_id>
</Authors>';

DECLARE @id INT;

DECLARE authors_cursor CURSOR FOR
SELECT n.c.value('.', 'INT') AS author_id
FROM @authors.nodes('/Authors/Author_id') AS n(c);

OPEN authors_cursor;

FETCH NEXT FROM authors_cursor INTO @id;

WHILE @@FETCH_STATUS = 0
BEGIN
  PRINT @id;                                -- do whatever you need with @id
  FETCH NEXT FROM authors_cursor INTO @id;
END

CLOSE authors_cursor;
DEALLOCATE authors_cursor; 

LiveDemo

How it works:

DECLARE authors_cursor CURSOR FOR
SELECT n.c.value('.', 'INT') AS author_id
FROM @authors.nodes('/Authors/Author_id') AS n(c);
  1. @authors.nodes('/Authors/Author_id') get nodes based on XQuery and alias for derived table as n and c - for column

  2. Use n.c.value('.', 'INT') AS author_id to get actual value of element