SQL Server with XML and selecting child nodes

Question

I have the following XML:

<tests>
   <test>1</test>
   <test>2</test>
   <test>3</test>
</tests>

And I am trying the following query:

CREATE PROCEDURE [dbo].[test]
    @Tests xml=null
AS
BEGIN
   SELECT
      doc.col.value('(test)[1]', 'nvarchar(50)')
   FROM 
      @Tests.nodes('//tests') AS doc(col)
END

But it only returns me a value from the first

What am I missing here?

Answer 1

The XPath in the nodes() function is only returning 1 record. Try this instead:

SELECT
  doc.col.value('.[1]', 'nvarchar(50)')
FROM @Tests.nodes('//test') AS doc(col)

Answer 2

If you want the <test> nodes, you need to use a different XPath in your .nodes()

SELECT
    doc.col.value('(.)[1]', 'nvarchar(50)')
FROM 
      @Tests.nodes('/tests/test') AS doc(col)

That way, you get an enumeration of all <test> nodes and you can select the inner XML from those using the (.) XPath.