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
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;
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);
@authors.nodes('/Authors/Author_id') get nodes based on XQuery and alias for derived table as n and c - for column
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.
Comments