XQuery/Formatting Numbers

Motivation

You want an easy way to format numbers by specifying the picture format of the number. So for example if you want to format numbers with a leading dollar sign, commas and two decimal places you would use the following "picture format":

   format-number($my-decimal, "$,000.00")

If the input number was 1234 the output would be $1,234.00

Method 1 - XSLT Wrapper

The format-number is a standard function in XSLT 1.0 and XPath 2.0. It is also included in the draft XQuery 1.1 Requirements To use this with eXist we will just write a wrapper to the Saxon XSLT format-number() function. To do this you will need to do the following:

download a copy of the Saxon9B XSLT program from http://prdownloads.sourceforge.net/saxon/saxonb9-1-0-2j.zip
unzip the package and copy three jar files (saxon9.jar, saxon9-dom.jar and saxon9-xpath.jar) into your your eXist lib/endorsed folder
comment out the following line in your eXist conf.xml file <transformer class="org.apache.xalan.processor.TransformerFactoryImpl"/>
un-comment the three lines that enable Saxon to be used as the default XSLT
restart your eXist server
add a function that will wrap a simple XSLT. See the example code below.

Source Code

We will create an XQuery function that takes two arguments. One decimal number and the second a string that specifies the picture format. We will pass these both to a small XSLT stylesheet.

(: the numeric picture format function from XPath 2.0.  To work with eXist we must enable Saxon as the default XSLT engine.  See the conf.xml file in the eXist folder for details. :)

declare function local:format-number($n as xs:decimal ,$s as xs:string) as xs:string {
    string(transform:transform(
      <any/>,
      <xsl:stylesheet xmlns:xsl="http://www.w3.org/1999/XSL/Transform" version="2.0">
         <xsl:template match='/'>
            <xsl:value-of select="format-number({$n},'{$s}')"/>
         </xsl:template>
      </xsl:stylesheet>,
      ()
    ))
};

Usage

The XSLT 1.0 format-number() function takes two arguments. The first is a decimal number and the second is a string that represents a picture of the output you desire. The format string is defined in the Java class DecimalFormat

If you want comma-separated values: local:format-number($my-decimal, ',000')

If you want leading dollar signs: local:format-number($my-decimal, '$,000')

The format of negative numbers is specified in a second picture format followed by a comma.

If you want negative numbers to have a minus sign: local:format-number($my-decimal, '0,000.00;-0,000.00')

Run tests

Run

Method 2 - XQuery function

Minollo posted an XQuery function in his blog. His code passed a suite of tests.

Test

Run tests

Discussion

It is our sincere hope that a future version of XQuery includes the functions to allow the developer to easily format both numeric and date formats.

Reference

Blog posting on XML Connections blog on format-number() written in XQuery