2

I am passing the following xml to a SQL Server stored procedure:

<root>
    <id>6063</id>
    <id>6064</id>
</root>

In my stored procedure I have the following code to select the id:

SELECT t.n.value('id[1]', 'int')
FROM @xml.nodes('/root') as t(n)

Now this works however it obviously only selects the first id and stops. I am not quite sure how to make this 'loop' and select all the id's in the xml file.

Any help greatly appreciated.

Here is the full stored procedure for reference:

BEGIN

DECLARE @xml xml = '<root>
                        <id>6063</id>
                        <id>6064</id>
                    </root>'

SELECT t.n.value('id[1]', 'int') as id
FROM @xml.nodes('/root') as t(n)

END
marc_s
  • 732,580
  • 175
  • 1,330
  • 1,459
Jamesla
  • 1,378
  • 7
  • 31
  • 62
  • It looks okay to me. As per **[this](http://stackoverflow.com/questions/5758091/how-to-loop-and-parse-xml-parameter-in-sql-server-stored-procedure)** SO article, it should work as written. Maybe you need to do a **t.n.value('id[1]', 'int') as 'ID'** as well? – kgdesouz Jul 03 '13 at 23:19
  • ok I have tried adding 'as id' however that hasn't helped. I will copy the sp into the question. – Jamesla Jul 03 '13 at 23:31
  • could anyone please tell why above code is not selecting all ids? – Purohit Hitesh Mar 22 '17 at 06:00

1 Answers1

4

This SQL should work for you:

DECLARE @xml xml = '<root>
                        <id>6063</id>
                        <id>6064</id>
                    </root>'

SELECT t.n.value('text()[1]', 'int') as id
FROM @xml.nodes('/root/id') as t(n)

The result from this query is:

id
----
6063
6064

Instead of selecting from the root element, I selected all id elements under root and used text() to read in the value.

Hope that helps.

rsbarro
  • 27,021
  • 9
  • 71
  • 75