xmlcsvxpathxslt

Generate CSV using XSLT from XML


Not able to generate CSV correctly using XSLT on the XML file.
My problem is to generate a currency rate file in csv that will be consumed by a connector.

Sample CSV file should be :
CalculateInverseRate,CalculateCrossRates,CrossRatesAnchorCurrency,EffectiveTimestamp,FromCurrency,TargetCurrency,CurrencyRateType,CurrencyRate 0,1,EUR,2023-11-22,EUR,USD,Current,1.0928 0,1,EUR,2023-11-22,EUR,JPY,Current,162.12 0,1,EUR,2023-11-22,EUR,BGN,Current,1.9558 0,1,EUR,2023-11-22,EUR,CZK,Current,24.541 0,1,EUR,2023-11-22,EUR,DKK,Current,7.4565 0,1,EUR,2023-11-22,EUR,GBP,Current,0.87630 0,1,EUR,2023-11-22,EUR,HUF,Current,378.90 0,1,EUR,2023-11-22,EUR,PLN,Current,4.3690 0,1,EUR,2023-11-22,EUR,RON,Current,4.9721 0,1,EUR,2023-11-22,EUR,SEK,Current,11.4270 0,1,EUR,2023-11-22,EUR,CHF,Current,0.9665 0,1,EUR,2023-11-22,EUR,ISK,Current,152.50 0,1,EUR,2023-11-22,EUR,NOK,Current,11.7200 0,1,EUR,2023-11-22,EUR,TRY,Current,31.4332 0,1,EUR,2023-11-22,EUR,AUD,Current,1.6669 0,1,EUR,2023-11-22,EUR,BRL,Current,5.3364 0,1,EUR,2023-11-22,EUR,CAD,Current,1.4994 0,1,EUR,2023-11-22,EUR,CNY,Current,7.8378 0,1,EUR,2023-11-22,EUR,HKD,Current,8.5165 0,1,EUR,2023-11-22,EUR,IDR,Current,16881.57 0,1,EUR,2023-11-22,EUR,ILS,Current,4.0811 0,1,EUR,2023-11-22,EUR,INR,Current,91.1060 0,1,EUR,2023-11-22,EUR,KRW,Current,1412.56 0,1,EUR,2023-11-22,EUR,MXN,Current,18.7590 0,1,EUR,2023-11-22,EUR,MYR,Current,5.1006 0,1,EUR,2023-11-22,EUR,NZD,Current,1.8132 0,1,EUR,2023-11-22,EUR,PHP,Current,60.497 0,1,EUR,2023-11-22,EUR,SGD,Current,1.4641 0,1,EUR,2023-11-22,EUR,THB,Current,38.494 0,1,EUR,2023-11-22,EUR,ZAR,Current,20.1445

<?xml version="1.0" encoding="UTF-8"?>
<gesmes:Envelope xmlns:gesmes="http://www.gesmes.org/xml/2002-08-01" xmlns="http://www.ecb.int/vocabulary/2002-08-01/eurofxref">
    <gesmes:subject>Reference rates</gesmes:subject>
    <gesmes:Sender>
        <gesmes:name>European Central Bank</gesmes:name>
    </gesmes:Sender>
    <Cube>
        <Cube time='2023-11-22'>
            <Cube currency='USD' rate='1.0928'/>
            <Cube currency='JPY' rate='162.12'/>
            <Cube currency='BGN' rate='1.9558'/>
            <Cube currency='CZK' rate='24.541'/>
            <Cube currency='DKK' rate='7.4565'/>
            <Cube currency='GBP' rate='0.87630'/>
            <Cube currency='HUF' rate='378.90'/>
            <Cube currency='PLN' rate='4.3690'/>
            <Cube currency='RON' rate='4.9721'/>
            <Cube currency='SEK' rate='11.4270'/>
            <Cube currency='CHF' rate='0.9665'/>
            <Cube currency='ISK' rate='152.50'/>
            <Cube currency='NOK' rate='11.7200'/>
            <Cube currency='TRY' rate='31.4332'/>
            <Cube currency='AUD' rate='1.6669'/>
            <Cube currency='BRL' rate='5.3364'/>
            <Cube currency='CAD' rate='1.4994'/>
            <Cube currency='CNY' rate='7.8378'/>
            <Cube currency='HKD' rate='8.5165'/>
            <Cube currency='IDR' rate='16881.57'/>
            <Cube currency='ILS' rate='4.0811'/>
            <Cube currency='INR' rate='91.1060'/>
            <Cube currency='KRW' rate='1412.56'/>
            <Cube currency='MXN' rate='18.7590'/>
            <Cube currency='MYR' rate='5.1006'/>
            <Cube currency='NZD' rate='1.8132'/>
            <Cube currency='PHP' rate='60.497'/>
            <Cube currency='SGD' rate='1.4641'/>
            <Cube currency='THB' rate='38.494'/>
            <Cube currency='ZAR' rate='20.1445'/>
        </Cube>
    </Cube>
</gesmes:Envelope>
<?xml version="1.0" encoding="UTF-8"?>
<xsl:stylesheet version="1.0" xmlns:xsl="http://www.w3.org/1999/XSL/Transform">
    <xsl:output method="text" />

    <!-- Static values -->
    <xsl:variable name="CalculateInverseRate" select="0"/>
    <xsl:variable name="CalculateCrossRates" select="1"/>
    <xsl:variable name="CrossRatesAnchorCurrency" select="'EUR'"/>
    <xsl:variable name="FromCurrency" select="'EUR'"/>
    <xsl:variable name="CurrencyRateType" select="'Current'"/>
    <xsl:variable name="EffectiveTimestamp" select="/gesmes:Envelope/Cube/Cube/@time"/>
    <xsl:variable name="newline">
        <xsl:text>
        </xsl:text>
    </xsl:variable>

    <xsl:template match="/">
        <xsl:value-of
            select="concat('CalculateInverseRate,CalculateCrossRates,CrossRatesAnchorCurrency,EffectiveTimestamp,FromCurrency,TargetCurrency,CurrencyRateType,CurrencyRate',$newline)" />
        <xsl:for-each select="//Cube">
            <xsl:for-each select="/Cube">
                <xsl:variable name="TargetCurrency" select="@currency"/>
                <xsl:variable name="CurrencyRate" select="@rate"/>
                <xsl:value-of
                    select="concat($CalculateInverseRate,',',$CalculateCrossRates,',',$CrossRatesAnchorCurrency,',',$EffectiveTimestamp,',',$FromCurrency,',',$TargetCurrency,',',$CurrencyRateType,',',$CurrencyRate,',',$newline)"/>
            </xsl:for-each>
        </xsl:for-each>
    </xsl:template>
</xsl:stylesheet>

Solution

  • If all you want from the XML is the date and the USD rate, why not do simply:

    XSLT 1.0

    <xsl:stylesheet version="1.0" 
    xmlns:xsl="http://www.w3.org/1999/XSL/Transform"
    xmlns:gesmes="http://www.gesmes.org/xml/2002-08-01" 
    xmlns:ecb="http://www.ecb.int/vocabulary/2002-08-01/eurofxref">
    <xsl:output method="text" />
    
    <xsl:template match="/gesmes:Envelope">
        <xsl:text>CalculateInverseRate,CalculateCrossRates,CrossRatesAnchorCurrency,EffectiveTimestamp,FromCurrency,TargetCurrency,CurrencyRateType,CurrencyRate  
    0,1,EUR,</xsl:text>
        <xsl:value-of select="ecb:Cube/ecb:Cube/@time" />
        <xsl:text>,EUR,USD,Current,</xsl:text>
        <xsl:value-of select="ecb:Cube/ecb:Cube/ecb:Cube[@currency='USD']/@rate" />
    </xsl:template>
    
    </xsl:stylesheet>
    

    Note the namespace declarations and the use of declared prefixes to select the elements in namespaces.


    Added:

    If (as you now say) you want a file that lists all currencies, you can do just as simply:

    <xsl:stylesheet version="1.0" 
    xmlns:xsl="http://www.w3.org/1999/XSL/Transform"
    xmlns:gesmes="http://www.gesmes.org/xml/2002-08-01" 
    xmlns:ecb="http://www.ecb.int/vocabulary/2002-08-01/eurofxref">
    <xsl:output method="text" />
    
    <xsl:template match="/gesmes:Envelope">
        <xsl:text>CalculateInverseRate,CalculateCrossRates,CrossRatesAnchorCurrency,EffectiveTimestamp,FromCurrency,TargetCurrency,CurrencyRateType,CurrencyRate
    </xsl:text>
        <xsl:variable name="date" select="ecb:Cube/ecb:Cube/@time" />
        <xsl:for-each select="ecb:Cube/ecb:Cube/ecb:Cube">
            <xsl:text>0,1,EUR,</xsl:text>
            <xsl:value-of select="$date" />
            <xsl:text>,EUR,</xsl:text>
            <xsl:value-of select="@currency" />
            <xsl:text>,Current,</xsl:text>
            <xsl:value-of select="@rate" />
            <xsl:text>
    </xsl:text>
        </xsl:for-each>
    </xsl:template>
    
    </xsl:stylesheet>