Prioritizing data while sorting in DbXML

Using XQuery in DBXML I want to prioritize some elements depending on multiple nodes set to certain values.

I want to be able to show three of this elements at the top and the rest below.

<properties>
   <property>
       <zip_code>5550</zip_code>
       <agency>ABC</agency>
   </property>
   <property>
       <zip_code>5550</zip_code>
       <agency>DEF</agency>
   </property>
   <property>
       <zip_code>5550</zip_code>
       <agency>DEF</agency>
   </property>
   <property>
       <zip_code>XYZ</zip_code>
       <agency>ABC</agency>
   </property>
</properties>

We are getting this XML in a property search page. Real search results will be having hundreds of records but we are only taking the first 10 records to display on the first page. Here we need to apply a sorting order which will show properties of "ABC" agency followed by zip code "XYZ" always on top. If the total result set does not have these agencies we can show them in the normal sorting order.

Solution

XQuery's flwor-expressions know order by, which can order by arbitrary values which can also be computed. Use an expression which decides if some product is a "top product" or not (resulting in a boolean value).

Afterwards split up result sequence to highlight only a number of results and limit to a total results.

let $highlighted := 3
let $total := 10
let $sorted := 
  for $p in //property
  (: order by highlighting predicate :)
  order by $p/agency eq "ABC" and $p/zip_code eq "XYZ" descending
  return $p
return (
  (: first $highlighted elements as defined by predicates above :)
  $sorted[ position() = (1 to $highlighted) ],
  (: the other elements, `/.` forces sorting back to document order :)
  $sorted[ position() = ($highlighted + 1 to $total) ]/.
)

The boolean expression can get arbitrary complex for being more precise on top products, like limiting to TVs or defining some minimum price.