Xquery Variable Issue - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2008 Forums

Transact-SQL (2008)

Xquery Variable Issue

Author

Topic

snipered2003
Starting Member

2 Posts

Posted - 2011-06-24 : 09:46:54

Hi i have an xquery. If i hard code a number such as 1 or 2, or 3 etc, then the query runs. However if i try to apply a variable it crashes. I cant't hard code it because it needs to loop round.
I i use the following i get:

Msg 2389, Level 16, State 1, Line 3
XQuery [table1.noData.value()]: 'value()' requires a singleton (or empty sequence), found operand of type 'xdt:untypedAtomic *'

DECLARE	     @intCount int
set          @intCount = 1
while        ( select TOP 1 noData.value('(/house/@Name)[sql:variable("@intCount")]','varchar(20)') FROM table1 ) <> 'test'
     BEGIN
	set @intCount = @intCount + 1
     END
print @intCount

I literally want to say the same as (without hardcoding it.)
select TOP 1 noData.value('(/house/@Name)[1]','varchar(20)'

OR

select TOP 1 noData.value('(/house/@Name)[2]','varchar(20)'

Does anyone know where i'm going wrong. P.s, i've also tried " + @intCount + " but that fails too.

sunitabeck
Master Smack Fu Yak Hacker

5155 Posts

Posted - 2011-06-24 : 11:07:05

Xquery value function is like a little child (picky and whiny), it wants no possibility that there would be more than one value returned ever. If you change your query to one of the following, hopefully it should work. Each of these would be functionally equivalent:

noData.value('((/house/@Name)[sql:variable("@intCount")])[1]','varchar(20)')

noData.value('(/house[sql:variable("@intCount")]/@Name)[1]','varchar(20)')

PS: No offense meant to any little child.

snipered2003
Starting Member

2 Posts

Posted - 2011-06-24 : 11:50:34

Hi thanks that worked perfectly.

Was struggling on that for ages.

sunitabeck
Master Smack Fu Yak Hacker

5155 Posts

Posted - 2011-06-24 : 12:25:20

you are very welcome! glad I could help.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources