CODEWITHSUNDEEP
sql-serverxmlt-sqlopenxml
sachaa
sachaa

Reputation: 1987

Why OPENXML only returns one element

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!

Upvotes: 3

Views: 5562

Answers (5)

C Jolly
C Jolly

Reputation: 1

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')

Upvotes: 0

Valerie
Valerie

Reputation: 111

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 = '<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

Upvotes: 11

Fahad
Fahad

Reputation: 43

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

Upvotes: 2

marc_s
marc_s

Reputation: 754348

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.

Upvotes: 9

Remus Rusanu
Remus Rusanu

Reputation: 294227

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);

Upvotes: 7

Related Questions