Enumerating nodes - problem - 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 2005 Forums

Transact-SQL (2005)

Enumerating nodes - problem

Author

Topic

monello
Starting Member

1 Post

Posted - 2008-08-13 : 11:31:06

I have following problem:


DECLARE @x XML

SET @x = 
'<MainNode>
  <Subnode Atrr1 = "1" />
  <Subnode Atrr1 = "2" />
  <Subnode Atrr1 = "3" />
  <Subnode Atrr1 = "4" />
</MainNode>'

DECLARE @max INT, @i INT, @atrValue int

SELECT @max = @x.query('<e>
                  { count(/MainNode/Subnode) }
                </e>').value('e[1]','int')

SET @i = 1

WHILE @i <= @max
BEGIN

SET @atrValue = @x.value('(/MainNode/Subnode/@Atrr1)[sql:variable("@i")]', 'int') // here is the problem

PRINT @atrValue
IF @atrValue = 1
    // do something
END

sql:variable("@i") - it doesn't work
the error message is

XQuery [value()]: 'value()' requires a singleton (or empty sequence), found operand of type 'xdt:untypedAtomic *'

When I type static number e.g. 2 the output is

But I want

If you know how to solve this problem please help me.
Thank you

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2008-09-03 : 07:03:45

[code]DECLARE @x XML,
@i INT,
@m INT,
@atrValue INT

SET @x = '
<MainNode>
<Subnode Atrr1 = "1" />
<Subnode Atrr1 = "2" >999</Subnode>
<Subnode Atrr1 = "3" />
<Subnode Atrr1 = "4" >-5</Subnode>
</MainNode>
'

SELECT @i = 1,
@m = @x.query('<e> { count(/MainNode/Subnode) } </e>').value('e[1]', 'INT')

WHILE @i <= @m
BEGIN
SELECT @atrValue = n.c.value('.', 'INT')
FROM @x.nodes('/MainNode/Subnode') AS n(c)
WHERE n.c.exist('.[@Atrr1 = sql:variable("@i")]') = 1

PRINT @i
PRINT @atrValue

IF @atrValue = 999
PRINT '******'
ELSE
PRINT '---'

SET @i = @i + 1
END[/code]

E 12°55'05.63"
N 56°04'39.26"

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources