>Home>Learn XQuery> XQuery Tips and Tricks>Numbering Records Print

Is there a way to number records extracted from a database?

I need to extract data from my relational database as XML, and I need to assign a number to each record that I extract. I wrote the following XQuery...

<myRecords> {
    let $i := 0
    for $Territories in collection("Northwind.dbo.Territories")/Territories
    let $i := $i + 1
    return
        <territory rec_count="{$i}">
            {$Territories/TerritoryDescription/text()}
        </territory>
} </myRecords>

... I don't get the result I expected:

<myRecords>
    <territory rec_count="1">Westboro</territory>
    <territory rec_count="1">Bedford</territory>
    <territory rec_count="1">Georgetown</territory>

…

The record number isn't incremented. Why?

XQuery is a functional language without side effects — you can't really create a notion of state in XQuery as you might in Java or C#, for example. In some cases, you can simulate state using a recursive function, but to solve this problem there is a much simpler solution: you can use a positional variable. Here's the XQuery:

<myRecords> {
    for $Territories at $i in collection("Northwind.dbo.Territories")/Territories
    return
        <territory rec_count="{$i}">
            {$Territories/TerritoryDescription/text()}
        </territory>
} </myRecords>

And here's the (hoped-for) result:

<myRecords>
    <territory rec_count="1">Westboro</territory>
    <territory rec_count="2">Bedford</territory>
    <territory rec_count="3">Georgetow</territory>

…

Next Question!

Can I use fn:collection() to retrieve the URL of a document in a folder?