FLWOR in Sql server count number of hits

Question

I am using SQL Server 2008 R2. My problem is that I want to count number of hits that i receive from XQuery query using FLWOR. For each hit, I want a consecutive number, like: 0,1,2,3,4...

My query:

select @xml.query('for $s at $count in /Root/Persons/Person
return <Person ID="{$count}">{$s}</Person>')

The only problem here is this is not supported in SQL Server and I receive an error:

Msg 9335, Level 16, State 1, Line 16
XQuery [query()]: The XQuery syntax 'at' is not supported.

I've also tried with let keyword and define new variable but I don't know how to increase value of that variable with each iteration?

Thanks for all the answers, Frenky

score 6 · Accepted Answer · edited May 23 '17 at 12:01

6

XQuery is a declarative language, you cannot use let to increment a counter.

A rather hackish workaround to the missing at feature would be to count the preceding sibling <person/> tags:

for $person in /Root/Persons/Person
let $count := count($person/preceding-sibling::Person) + 1
return <Person ID="{$count}">{$person}</Person>

Be aware that this code will have O(n^2) runtime if not optimized by the execution engine (which it will probably not do) because of the repeated preceding sibling scans.

Edit: As stated in the comments, MS SQL doesn't even support the preceding-sibling axis. They do support the << node order comparison operator, though. This query should be fully supported:

for $person in /Root/Persons/Person
let $count := count($person/parent::Persons/Person[. << $person]) + 1
return <Person ID="{$count}">{$person}</Person>

By the way, you possibly only want to paste the person's name, so better use

(: snip :)
return <Person ID="{$count}">{data($person)}</Person>

edited May 23 '17 at 12:01

Community

1
1

answered Sep 10 '13 at 11:39

Jens Erat

37,523
16
80
96

Thanks a lot for answer. In Sql server 2008R2, preceding-sibling is also not supported, the error I've received: Msg 9335, Level 16, State 1, Line 16 XQuery [query()]: The XQuery syntax 'preceding-sibling' is not supported. – FrenkyB Sep 10 '13 at 12:01
1

Wow, didn't expect Microsoft had crippled XQuery that much. I extended my answer with another even more ugly workaround which should be supported. – Jens Erat Sep 10 '13 at 13:44
It works )) The syntax is as ugly as it could be and far from intuitive, but it works )) Thank you again. – FrenkyB Sep 11 '13 at 05:35

adamretter · Answer 2 · 2014-12-10T15:17:55.607

0

Another possible formulation that may arguably be easier to read if you are not so familiar with XQuery:

for $i in (1 to count(/Root/Persons/Person))
let $person := /Root/Persons/Person[$i]
return
    <Person ID="{$i}">{$person}</Person>

Also if SQL Server does/ever support(s) XQuery 3.0, then you could do the following which is rather nice:

/Root/Persons/Person ! <Person ID="{position()}">{data(.)}</Person>

edited Dec 10 '14 at 15:17

answered Sep 23 '14 at 13:56

adamretter

3,885
2
23
43

1

Just to compliment: At SQL Server 2005 I am having the error: The XQuery syntax 'to' is not supported. – Tito Dec 05 '14 at 23:20

FLWOR in Sql server count number of hits

2 Answers2

Linked