asked on

SQL Server XQuery Help 'value()' requires a singleton

I am trying to write an xquery passing dynamic values .Here is my code
Here if I pass static value [1] or [2] I get the value in the tag <Method>.What I am trying to do is to loop through it to print all the values in <method> tag.For that if I pass the value @cnt,i get this error

Msg 2389, Level 16, State 1, Line 13
XQuery [value()]: 'value()' requires a singleton (or empty sequence), found operand of type 'xdt:untypedAtomic *'
How do you pass value dynamically as in the code

DECLARE @myxml XML
Select @myxml = '<Root xmlns="http"><Name>Test</Name><Methods><Method>Air</Method><Method>Ground</Method></Methods></Root>'
DECLARE     
	@cnt INT,     
	@totCnt INT   , 
	@Value VARCHAR(10)	
SELECT     @cnt = 1    
SET @totCnt = (Select @myxml.value('declare namespace x="http";count(/x:Root/x:Methods/x:Method)', 'INT'))

WHILE @cnt <= @totCnt 
BEGIN    

Set @Value =(SELECT @myxml.value('declare namespace x="http";(/x:Root/x:Methods/x:Method)[@cnt]', 'nvarchar(100)') )
Print @Value
		SELECT @cnt = @cnt + 1
END

Open in new window

Christopher Gordon

Just a quick observation, It *looks* like @cnt is wrapped in single quotes and won't be interpreted as the value of the variable but rather the variable name '@cnt' itself. I'll verify in a sec.

spprivate

ASKER

thanks.fyi i tried using [position()=sql:variable("@cnt")] as well

ASKER CERTIFIED SOLUTION

Christopher Gordon

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

spprivate

ASKER

Thanks ,that works..Just curious.Full points to you
how would you fetch an attribute in the same manner.Say something like
<Method name="transport"> Air </Method>
I want to select "transport"

spprivate

ASKER

Never mind.Got that with data() .Thanks a lot.Just couldnt follow much.Appreciate if you can explain it tear down.