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

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 2

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

Answer 3

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

友情链接