SQL XML not returning data

Question

I am using the following code It is not returning me results. What am i missing here?

    declare @iDoc INT           
  EXEC sp_xml_preparedocument @iDoc OUTPUT, '<Ids><Id Val ="40041">11</Id><Id Val ="40042">22</Id></Ids>'   
  Select @iDoc       
  Select Val FROM OPENXML (@iDoc, '/Ids/Id',2) WITH (Val varchar(10))

Ankit · Accepted Answer · 2011-11-22 14:56:32Z

1

Found answer - Replace '/Ids/Id',2 with '/Ids/Id',5

answered Nov 22, 2011 at 14:56

Ankit

6,7368 gold badges57 silver badges80 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

George Mastros · Accepted Answer · 2011-11-22 16:20:52Z

1

I prefer to use xpath in the WITH part of the query. This way, you don't need to worry about the 3rd parameter of the OPENXML function. The following code works regardless of the 3rd parameter (it can be any value).

declare @iDoc INT           

EXEC sp_xml_preparedocument @iDoc OUTPUT, '<Ids><Id Val ="40041">11</Id><Id Val ="40042">22</Id></Ids>'   

Select *
FROM   OPENXML (@iDoc, '/Ids/Id',2) 
WITH   (Val varchar(10) '@Val', 
       Id int '.')

EXEC sp_xml_removedocument @iDoc

answered Nov 22, 2011 at 16:20

George Mastros

24.6k5 gold badges56 silver badges64 bronze badges

Collectives™ on Stack Overflow

SQL XML not returning data

2 Answers 2

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related