Convert string in scientific notation to number format in XPath

XPath 1.0 solution

If you are stuck with XPath 1.0 and your input is of the form (mantissa E+ exponent) or (mantissa E- exponent) you can use this hack (which is not very nice but gets the job done):

translate(     concat(           number(substring('100000000000000',1,number(substring-after(/number,'E+'))+1))*number(substring-before(/number,'E+')),           number(concat(substring('0.00000000000000',1,number(substring-after(/number,'E-'))+1),'1'))*number(substring-before(/number,'E-')),           number(substring('100000000000000',1,number(substring-after(/number,'e+'))+1))*number(substring-before(/number,'e+')),           number(concat(substring('0.00000000000000',1,number(substring-after(/number,'e-'))+1),'1'))*number(substring-before(/number,'e-')),           /number[not(contains(.,'E')) and not(contains(.,'e'))]     ), 'Na', '')

It extracts the mantissa (string before the E or e) and then obtains a fraction or power of 10 by shifting a string of zeros the amount of positions determined by the exponent, Then it multiplies this with the mantissa. It deals separately with the case of positive or negative exponents. Since there is no if in XPath, it concatenates both results as strings, and allows one of them to produce 'NaN, which is removed later.

For example, if the exponent is +2 it will get the 100 substring from the first or third string. If it is +5 it will get 10000 and if it is -3 it will extract the 0.001 substring from the second or fourth strings. It then will multiply that number with the mantissa.

Since you will either have e+ or e-, one of them will be the string NaN which is then removed from the final string using the translate function (it removes all N and a characters, which never occur in decimal numbers or scientific notation). The last concat argument deals with the case where the number is not in scientific notation.

The e can be in upper or lower case.

Limitations:

1. It won't work if you have positive exponents without the +
2. It will show incorrect results if the absolute value of the exponent is larger than 15.
3. It also will fail if you have hexadecimal values (since any a will be removed from the final result).

Applying it to this file:

<number>2.34</number>

it will read normally as:

2.34

But 2.34e+5 will be read as 234000 and 2.34E-005 will become 0.0000234.

You can use the function number(). This works with xpath 2.0.

Here is an example of how it would work.

XML

<this><stuff>8.1161E-002</stuff></this>

Xpath

/this/number(stuff)

Result

0.081161

Hope this helps!

I've had the same issue on Xpath > 1.0. Just for completeness I solved mine with:

xs:decimal(sum(/a/b))

Works fine