Interstage Shunsaku Data Manager Application Development Guide - Microsoft(R) Windows(R) 2000/ Microsoft(R) Windows Server(TM) 2003 - - UNIX -

3.5.2 Sorting by Numeric Value

It is possible to sort character strings in element nodes of XML documents or character strings in the attribute nodes of those element nodes as if they were numeric values. To do so, specify the single-line val function in the key specification in the sort expression. A text expression or attribute expression is specified as the argument of the val function.

Refer to B.2.4 Single-line Function Specification for more information on single-line function specifications.

The explanation that follows assumes that Documents A, B, C, and D below exist:

Document A

<doc>
  <basic expense="$76.50">
    :
  </basic>
  <detail>
    :
    <taxi>$18.90</taxi>
    :
  </detail>
</doc>

Document B

<doc>
  <basic expense="$129.80">
    :
  </basic>
  <detail>
    :
    <taxi>$111.50</taxi>
    :
  </detail>
</doc>

Document C

<doc>
  <basic expense="No trip expenses required">
    :
  </basic>
  <detail>
    :
    <taxi>$37.50</taxi>
    :
  </detail>
</doc>

Document D

<doc>
  <basic expense="$4.80">
    :
  </basic>
  <detail>
    :
    <taxi>$74.00</taxi>
    :
  </detail>
</doc>

Example 1

The following example shows how to sort documents in ascending order according to the taxi fares (taxi):

val(/doc/detail/taxi/text())

Result: Documents are returned in the order A, C, D, B.

Example 2

The following example shows how to sort documents in ascending order according to the trip expenses (expense):

val(/doc/basic/@expense)

Result: Documents are returned in the order C, D, A, B.

If the character string in the text node in the XML document specified by the text expression does not contain a numeric value, the val function will treat the value of the node as 0. In this example, the trip expenses in Document C will be treated as 0.

If the text node in the XML document specified by the text expression does not exist, that XML document will be returned last.

Specifying 'DESC' after the key specification will sort numeric values in descending order.

Example 3

The following example shows how to sort documents in descending order according to the taxi fares (taxi):

val(/doc/detail/taxi/text()) DESC

Result: Documents are returned in the order B, D, C, A.

Example 4

The following example shows how to sort documents in descending order according to the trip expenses (expense):

val(/doc/basic/@expense) DESC

Result: Documents are returned in the order B, A, D, C.