1

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.

Rcrd 009
  • 323
  • 6
  • 17
  • Have you had a look at `order by` in XQuery? If yes, please be more specific on your problem, include example data and desired output and if you've got anything some example code. – Jens Erat Jan 09 '13 at 13:00
  • @Ranon thanks for the response. I have hundreds of data to sort and say "brand" is one field in that database. I am doing this order by while filtering some data. If the filtered data is having "Brand=Sony", this should always come at the top of the result set. For example, you have some featured products and you want to show those products at the top always Please let me know if you need more details. Again thanks in advance – Rcrd 009 Jan 09 '13 at 14:30
  • Much better now, would you mind updating your question? For future questions, please include some sample data/code, this is much more precise than some text only (which additionally lacks proper punctation). You're asking for others to spend time on helping you for free; please make it as easy as possible for them - you will find much more people willing to help you. – Jens Erat Jan 09 '13 at 15:46
  • @Ranon sorry for not being detailed in my question as I was heading off to something else after posting this.. I am going to edit the questions now – Rcrd 009 Jan 09 '13 at 16:30

1 Answers1

3

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.

Jens Erat
  • 37,523
  • 16
  • 80
  • 96
  • I updated my answer to fit your question. I edited this, too to be more precise and reduced it to the actual question; maybe have a look on it if it still resembles your problem. – Jens Erat Jan 09 '13 at 18:07
  • the information was really helpful, we are almost done but have one more issue. I have updated the question. Can you please check? – Rcrd 009 Jan 11 '13 at 09:15
  • Sorry, I thought it posted. Somehow it was not saved.. Please give me few min as I am editing the question now – Rcrd 009 Jan 11 '13 at 12:08
  • 1
    Please, give a little bit more attention on readable code; squeezing whole flwor-expressions in single lines which get wrapped multiple times definitly is bad coding standard (and without formatting, at least I don't understand anything of that code). If you really want answers, put more effort in your questions. – Jens Erat Jan 11 '13 at 16:33