looping through XML with xquery

Hello,

for example:

declare @.xml xml
select @.xml = (select * from table for xml raw, elements)

... now i want to iterate through @.xml and get the values from field ID:

declare @.id int, @.x int, @.y int
select @.x = @.xml.value('data(count(/*))','int')
set @.y = 1
while @.y <= @.x begin
select @.id = @.xml.value('data(/row/ID)[' + cast(@.y as varchar) + ']','int')
set @.y * @.y + 1
end

... this is not working because for value() only string literals are allowed, so how can i do this?

thank you,
Helmut

You are on the right track, but you need to use the sql:variable function to do this. sql:variable gives you access to variables and parameters in scope.

http://msdn2.microsoft.com/en-us/library/ms188254.aspx

so you should be able to write your query as:

select @.id = @.xml.value('data(/row/ID)[sql:variable(@.y)]','int')

This also has the added advantage of avoiding sql or xquery injection.

|||Superb, works perfect!

thank you very much,
Helmut
|||SELECT SearchCriteriaXML.value('data(//ColumnName)[sql:variable(@.i)]','varchar(100)')
FROM SearchColumn INNER JOIN SearchCriteria ON SearchColumn.SearchCriteriaId = SearchCriteria.Id
WHERE (SearchCriteria.Id = 1) AND (SearchColumn.Id = 1)

I'm Getting below error for above query

Msg 2225, Level 16, State 1, Line 20
XQuery [SearchCriteria.SearchCriteriaXML.value()]: A string literal was expected
sql