Why OPENXML only returns one element

Question

Can somebody explain me why this T-SQL code only returns one row with the value "1"? I was expecting to get two rows ("1" and "2"). Am I missing something here?

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'm using SQL Server 2008

Thanks a lot!

Answer 1

Forget about the openxml non-sense, is slow, cumbersome, unusable and bad all around. Use the XML methods, they're fast, intuitive, user friendly and oozing with goodness:

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 2

Why don't you use the new .nodes() method on XML variables in SQL Server 2005 and up??

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 the '1' and '2' value as expected.

Answer 3

use this instead ...

Your xpath is to ids so id is an attribute of ids but this is not the case so you need to specify the xpath by going up the tree using .. then specify the node. a node is specified by ../id and an attribute by ../@whatever

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

Answer 4

I had the same problem and I found the answer on another site. You have to use '/ids/id' instead of '/ids' and then use '.' in the WITH clause.

DECLARE @XMLDoc2 XML SELECT @XMLDoc2 = '12'

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