How to implement paging in T-Sql XQuery

Question

I have some XML, very simple in structure, but in some cases extremely large.

<group>
  <thing attr1="val1" attr2="val2"/>
  <thing attr1="val3" attr2="val4"/>
  ...
</group>

The following simple query works very effectively to return a subset of things

select MyXml.query('//group/thing[contains(@attr2,"val")]')
from mytable 
where pkfield = nnn

In fact it is astonishingly performant.

However, I need to be able to return things in chunks of (say) 25 at a time. In other words I need to be able to plug in StartFrom & Pagesize variables. This presents 2 headaches

1. How and when to sort (I probably will ensure the nodes are presorted in the xml, but any alternatives would be gratefully received)

2. How to implement the paging?

I have tried Position() and [1 to 25] but without success. Perhaps I just do not have the syntax right.

Answer 1

I found that the following is effective

select MyXml.query('for $thing in //group/thing[contains(@attr2,"val")]
       [position() >   sql:variable("@PageIndex")      * sql:variable("@PageSize") and
        position() <= (sql:variable("@PageIndex") + 1) * sql:variable("@PageSize")]')
from mytable 
where pkfield = nnn