N. America: 800 876 3101 | World: 44 (0) 1753 218 930

Can I instruct XQuery to use numbers and not scientific notation in query results?

My database has a "Quantity" column defined as type "real". When I run this XQuery:

<bigOnes> {
    for $Invoices in collection("Invoices")/Invoices
    where $Invoices/Quantity > 1000
    return $Invoices/Quantity
} </bigOnes>

I get this result:

<bigOnes>

    <Quantity>1.0E7</Quantity>

</bigOnes>

Considering that the "Quantity" column never uses decimals, it doesn't really make sense to display my results this way. How can I instruct the XQuery processor to not use scientific notation?

The simplest way is probably to force "Quantity" to be interpreted as an xs:integer type, which you can do like this:

<bigOnes> {
    for $Invoices in collection("Invoices")/Invoices
    where $Invoices/Quantity > 1000
    return <Quantity>{xs:integer($Invoices/Quantity)}</Quantity>
} </bigOnes>

This will get you the result you're looking for:

<bigOnes>

   <Quantity>10000000</Quantity>

</bigOnes>


Next Question!

Why do I get an error trying to extract values from a function returning XML?

Submit Your DataDirect XQuery Tip or Trick

Tell us your XQuery Tip or Trick – if it gets published on our site, you’ll receive a

$10.00 Amazon.com
Gift Certificate!

Submit your tip or trick today.