'xslt - how to remove grouping-separator/reformat number to correct xs:double type?

I'm currently working with inconsistent data and I'm having issue with converting few values so that they are actually consistent.

This is sample from the XML file that I have to work with:

<RootRequest>   
    <Data>
        <APPLICATION_ID>3158997</APPLICATION_ID>
        <APPLICATION_DATE>1.9.2019</APPLICATION_DATE>
        <AMOUNT>48597.95</AMOUNT>
        <FINANCED_AMOUNT>48597.95</FINANCED_AMOUNT>
        <DOWNPAYMENT>0</DOWNPAYMENT>
        <RESIDUAL_VALUE>27390.9</RESIDUAL_VALUE>
        <DURATION>36</DURATION>
    </Data>
    <Data>
    <APPLICATION_ID>4127974</APPLICATION_ID>    
        <APPLICATION_DATE>1.9.2019</APPLICATION_DATE>
    <AMOUNT>22.800,00</AMOUNT>
    <FINANCED_AMOUNT>17.300,00</FINANCED_AMOUNT>
    <DOWNPAYMENT>5.500,00</DOWNPAYMENT>
    <RESIDUAL_VALUE>3.000,00</RESIDUAL_VALUE>
    <DURATION>24</DURATION>
    </Data>
    <Data>
    <APPLICATION_ID>3158995</APPLICATION_ID>
    <APPLICATION_DATE>1.9.2019</APPLICATION_DATE>
    <AMOUNT>23903.55</AMOUNT>
    <FINANCED_AMOUNT>23903.55</FINANCED_AMOUNT>
    <DOWNPAYMENT>0</DOWNPAYMENT>
    <RESIDUAL_VALUE>23073.55</RESIDUAL_VALUE>
    <DURATION>6</DURATION>
    </Data>
    <Data>
    <APPLICATION_ID>4127974</APPLICATION_ID>
    <APPLICATION_DATE>1.9.2019</APPLICATION_DATE>
    <AMOUNT>52.800,00</AMOUNT>
    <FINANCED_AMOUNT>77.300,00</FINANCED_AMOUNT>
    <DOWNPAYMENT>5500.84</DOWNPAYMENT>
    <RESIDUAL_VALUE>3000.25</RESIDUAL_VALUE>
    <DURATION />
    </Data>
</RootRequest>

as you can see there are inconsistencies for example in element FINANCED_AMOUNT you can see that data differs... in one example it is 17.300,00 in the other it is 23903.55 and I need to have them all be like the second example... so that would be 17300.00 and not 17.300,00...

I have tried using format-number() with xsl:decimal-format:

<xsl:decimal-format name="decimals" decimal-separator="." grouping-separator="," />

format-number(DOWNPAYMENT, '###.###,00', 'decimals')

but it doesnt work... it returns an error saying

invalid lexical value - cannot convert parameter at position [1] of the format-number function. I wanted to try removing thousand separator but dont know if it is possible...

here is the XSLT:

<xsl:stylesheet version="2.0" xmlns:xsl="http://www.w3.org/1999/XSL/Transform" xmlns:xs="http://www.w3.org/2001/XMLSchema" xmlns:fn="http://www.w3.org/2005/xpath-functions">
    <xsl:output method="xml" version="1.0" encoding="UTF-8" indent="yes"/>
    <xsl:decimal-format name="decimals" decimal-separator="." grouping-separator="," />
    <xsl:template match="RootRequest">
        <xsl:element name="Requests">
            <xsl:for-each select="Data">
                <xsl:call-template name="prepRequest" />
            </xsl:for-each>
        </xsl:element>
    </xsl:template>
        <xsl:template name="prepRequest">
        <xsl:element name="Request">
                        <xsl:element name="Amount">
                <xsl:value-of select="format-number(FINANCED_AMOUNT, '###.###,00', 'decimals')" />
                        </xsl:element>
        </xsl:element>
    </xsl:template>
</xsl:stylesheet>

Can someone help me with this topic?

xmlxsltdecimalxslt-1.0xslt-2.0


Solution 1:[1]

You can check e.g.

<xsl:template match="*[not(*)][normalize-space()][not(. castable as xs:decimal)]">
  <xsl:copy>
    <xsl:value-of select=". => translate(',.', '.') => xs:decimal() => format-number( '0.00')"/>
  </xsl:copy>
</xsl:template>

Sources

This article follows the attribution requirements of Stack Overflow and is licensed under CC BY-SA 3.0.

Source: Stack Overflow

Solution Source
Solution 1 Martin Honnen

Related Questions