Why OPENXML returns only one element

Question

Can someone explain to me why this T-SQL code returns only one row with a value of "1"? I expected to get two lines ("1" and "2"). Did I miss something?

DECLARE @XMLDoc2 XML
SELECT @XMLDoc2 = '<ids><id>1</id><id>2</id></ids>'

DECLARE @handle2 INT

EXEC sp_xml_preparedocument @handle2 OUTPUT, @XMLDoc2

SELECT * FROM OPENXML (@handle2, '/ids', 2) WITH (id INT 'id') 

EXEC sp_xml_removedocument @handle2

NOTE : I am using SQL Server 2008

Thanks a lot!

Answer 1

Why aren't you using the new .nodes () method for XML variables in SQL Server 2005 and later?

DECLARE @XMLDoc2 XML
SELECT @XMLDoc2 = '<ids><id>1</id><id>2</id></ids>'


SELECT
   ids.id.value('.', 'int') 'id'
FROM 
   @xmldoc2.nodes('/ids/id') ids(id)

This gives me a value of "1" and "2" as expected.

Answer 2

, . '/ids/id' '/ids', '.' WITH.

DECLARE @XMLDoc2 XML 
SELECT @XMLDoc2 = '<ids><id>1</id><id>2</id></ids>' 

DECLARE @handle2 INT 

EXEC sp_xml_preparedocument @handle2 OUTPUT, @XMLDoc2 

SELECT * FROM OPENXML (@handle2, '/ids/id', 2) WITH (id INT '.')  

EXEC sp_xml_removedocument @handle2 

Answer 3

Forget about obscure openxml, slow, bulky, unusable and bad around. Use XML methods , they are fast, intuitive, easy to use and leaked:

DECLARE @XMLDoc2 XML
SELECT @XMLDoc2 = '<ids><id>1</id><id>2</id></ids>'

select x.value(N'.', N'int') as id
from @XMLDoc2.nodes(N'/ids/id') t(x);

Answer 4

DECLARE @XMLDoc2 XML
SELECT @XMLDoc2 = '<ids><id>1</id><id>2</id></ids>'
DECLARE @handle2 INT
EXEC sp_xml_preparedocument @handle2 OUTPUT, @XMLDoc2
SELECT * FROM OPENXML (@handle2, '/ids/id', 2) WITH (id INT '.') 
EXEC sp_xml_removedocument @handle2

Answer 5

Your xpath is identifiers, so id is an attribute of identifiers, but it is not, so you need to specify xpath by going through the tree with .. then specify node. a node is specified ../idand attribute../@whatever

SELECT * FROM OPENXML (@handle2, '/ids', 2) WITH (id INT '../id')