SQL Server with XML and selecting child nodes
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
@Test开发者_如何学JAVAs.nodes('//tests') AS doc(col)
END
But it only returns me a value from the first
What am I missing here?
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.
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)
精彩评论