XQuery/Formatting Numbers

Motivation edit

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 edit

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:

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

Source Code edit

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 edit

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 edit

Run

Method 2 - XQuery function edit

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

Test edit

Run tests

Discussion edit

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 edit

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