0

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>
Improve this question

2 Answers 2

Reset to default
1 

<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" />

    <!-- 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/ecb:Cube/ecb:Cube/@time"/>-->
    <xsl:variable name="newline">
    <xsl:text>
</xsl:text>
    </xsl:variable>

<xsl:template match="/gesmes:Envelope">
    <xsl:value-of
            select="concat('CalculateInverseRate,CalculateCrossRates,CrossRatesAnchorCurrency,EffectiveTimestamp,FromCurrency,TargetCurrency,CurrencyRateType,CurrencyRate',$newline)" />
    <xsl:variable name="EffectiveTimestamp" select="ecb:Cube/ecb:Cube/@time"/>
    
    <xsl:for-each select="ecb:Cube/ecb:Cube/ecb: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:template>

</xsl:stylesheet>

Improve this answer
Sign up to request clarification or add additional context in comments.

Comments

0

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>
Improve this answer

2 Comments

Hello michael.hor257k, my aim is to loop for all <Cube> elements the XML file received and render a CSV file that will list all the currencies. Example: see above csv
Hello michael.hor257k, your analysis brought light on my issue, i was able to generate the csv using the namespace declaration and the prefixes. I modified the above XSLT code and it worked.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.