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

Dimitris Poulopoulos

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
Lukasz Szozda

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

Collected from the Internet

Please contact [email protected] to delete if infringement.

edited at
0

Comments

0 comments
Login to comment

Related

From Dev

How can I use the OUT parameter from a SQL Server stored procedure in a vb.net code

From Dev

In SQL Server, should I create synonym for a table or a stored procedure?

From Dev

How to create a cursor in Mysql Within Stored procedure

From Dev

Create text file from stored procedure SQL Server 2008

From Dev

How can I merge 3 distinct Sqls by the same grouping but different columns (Sql Server Stored Procedure)

From Dev

how can I get cursor data with calling stored procedure in npgsql

From Dev

How to create stored procedure in C#, then *save* it to SQL Server?

From Dev

SQL Server Stored Procedure XML can't get node right

From Dev

How can I write two update queries in single stored procedure in SQL Server 2008

From Dev

How can I pass and process an array of varchars as parameters to/within a SQL Server stored procedure parameter?

From Dev

How to create a stored procedure in SQL Server Management Studio

From Dev

Create stored procedure in SQL Server from a MS Access update query

From Dev

How can I use the OUT parameter from a SQL Server stored procedure in a vb.net code

From Dev

In SQL Server, should I create synonym for a table or a stored procedure?

From Dev

How to create a cursor in Mysql Within Stored procedure

From Dev

How can I make it so a where clause with a value of 0 makes it not check the where in a SQL Server stored procedure?

From Dev

How to create a select, and then an update stored procedure in SQL Server 2012

From Dev

How can I claim ranges of integers to assign to an entity using a SQL Server stored procedure?

From Dev

How to create dynamic parameters in SQL Server stored procedure

From Dev

How to create a stored procedure on SQL server 2014 for create table operation?

From Dev

How can I back up a stored procedure in SQL Server?

From Dev

How to figure out why is the cursor failing in a stored procedure in SQL Server?

From Dev

SQL Server : Insert cursor within stored procedure

From Dev

How can I pass and process an array of varchars as parameters to/within a SQL Server stored procedure parameter?

From Dev

How to create a stored procedure in SQL Server Management Studio

From Dev

How can I test stored procedure errors in SQL Server

From Dev

Create stored procedure on SQL Server

From Dev

How can I stop Hibernate's SQLExceptionConverter from converting error messages stored in a SQL Server procedure?

From Dev

How to DROP and CREATE a table in SQL Server 2016 Stored Procedure

Related Related

  1. 1

    How can I use the OUT parameter from a SQL Server stored procedure in a vb.net code

  2. 2

    In SQL Server, should I create synonym for a table or a stored procedure?

  3. 3

    How to create a cursor in Mysql Within Stored procedure

  4. 4

    Create text file from stored procedure SQL Server 2008

  5. 5

    How can I merge 3 distinct Sqls by the same grouping but different columns (Sql Server Stored Procedure)

  6. 6

    how can I get cursor data with calling stored procedure in npgsql

  7. 7

    How to create stored procedure in C#, then *save* it to SQL Server?

  8. 8

    SQL Server Stored Procedure XML can't get node right

  9. 9

    How can I write two update queries in single stored procedure in SQL Server 2008

  10. 10

    How can I pass and process an array of varchars as parameters to/within a SQL Server stored procedure parameter?

  11. 11

    How to create a stored procedure in SQL Server Management Studio

  12. 12

    Create stored procedure in SQL Server from a MS Access update query

  13. 13

    How can I use the OUT parameter from a SQL Server stored procedure in a vb.net code

  14. 14

    In SQL Server, should I create synonym for a table or a stored procedure?

  15. 15

    How to create a cursor in Mysql Within Stored procedure

  16. 16

    How can I make it so a where clause with a value of 0 makes it not check the where in a SQL Server stored procedure?

  17. 17

    How to create a select, and then an update stored procedure in SQL Server 2012

  18. 18

    How can I claim ranges of integers to assign to an entity using a SQL Server stored procedure?

  19. 19

    How to create dynamic parameters in SQL Server stored procedure

  20. 20

    How to create a stored procedure on SQL server 2014 for create table operation?

  21. 21

    How can I back up a stored procedure in SQL Server?

  22. 22

    How to figure out why is the cursor failing in a stored procedure in SQL Server?

  23. 23

    SQL Server : Insert cursor within stored procedure

  24. 24

    How can I pass and process an array of varchars as parameters to/within a SQL Server stored procedure parameter?

  25. 25

    How to create a stored procedure in SQL Server Management Studio

  26. 26

    How can I test stored procedure errors in SQL Server

  27. 27

    Create stored procedure on SQL Server

  28. 28

    How can I stop Hibernate's SQLExceptionConverter from converting error messages stored in a SQL Server procedure?

  29. 29

    How to DROP and CREATE a table in SQL Server 2016 Stored Procedure

HotTag

Archive