Creating XSL scheme for exchange rates

Question

I need to download daily exchange rates of some currencies to my database.

This is the XML data:

<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="2022-12-23">
<Cube currency="USD" rate="1.0622"/>
<Cube currency="JPY" rate="140.86"/>
<Cube currency="BGN" rate="1.9558"/>
<Cube currency="CZK" rate="24.247"/>
<Cube currency="DKK" rate="7.4364"/>
<Cube currency="GBP" rate="0.88030"/>
<Cube currency="HUF" rate="400.68"/>
<Cube currency="PLN" rate="4.6423"/>
<Cube currency="RON" rate="4.9056"/>
<Cube currency="SEK" rate="11.1045"/>
<Cube currency="CHF" rate="0.9867"/>
<Cube currency="ISK" rate="152.30"/>
<Cube currency="NOK" rate="10.4448"/>
<Cube currency="HRK" rate="7.5370"/>
<Cube currency="TRY" rate="19.8430"/>
<Cube currency="AUD" rate="1.5857"/>
<Cube currency="BRL" rate="5.4834"/>
<Cube currency="CAD" rate="1.4433"/>
<Cube currency="CNY" rate="7.4198"/>
<Cube currency="HKD" rate="8.2878"/>
<Cube currency="IDR" rate="16569.18"/>
<Cube currency="ILS" rate="3.7040"/>
<Cube currency="INR" rate="87.9580"/>
<Cube currency="KRW" rate="1359.50"/>
<Cube currency="MXN" rate="20.7115"/>
<Cube currency="MYR" rate="4.7002"/>
<Cube currency="NZD" rate="1.6887"/>
<Cube currency="PHP" rate="58.623"/>
<Cube currency="SGD" rate="1.4337"/>
<Cube currency="THB" rate="36.842"/>
<Cube currency="ZAR" rate="18.1048"/>
</Cube>
</Cube>
</gesmes:Envelope>

I already have some XSL scheme from my other project but it was XML with no attributes and I'm not able to fit it...I guess this is really simple for someone who knows the XML, but that is unfortunately not my case...

This is the XSL I already have (I chose only few currencies):

<?xml version="1.0" encoding="UTF-8"?>
<xsl:stylesheet version="1.0" xmlns:xsl="http://www.w3.org/1999/XSL/Transform">

<xsl:output method="xml" version="1.0" encoding="utf-8" indent="yes"/>

<xsl:template match="/">

<FMPXMLRESULT xmlns="http://www.filemaker.com/fmpxmlresult">
<ERRORCODE>0</ERRORCODE>
<PRODUCT BUILD="" NAME="" VERSION=""/>
<DATABASE DATEFORMAT="" LAYOUT="" NAME="" RECORDS="" TIMEFORMAT=""/>

<METADATA>
<FIELD NAME="Date" TYPE="TEXT" EMPTYOK="YES" MAXREPEAT=""/>
<FIELD NAME="USD" TYPE="TEXT" EMPTYOK="YES" MAXREPEAT=""/>
<FIELD NAME="JPY" TYPE="TEXT" EMPTYOK="YES" MAXREPEAT=""/>
<FIELD NAME="CZK" TYPE="TEXT" EMPTYOK="YES" MAXREPEAT=""/>
<FIELD NAME="GBP" TYPE="TEXT" EMPTYOK="YES" MAXREPEAT=""/>
<FIELD NAME="HUF" TYPE="TEXT" EMPTYOK="YES" MAXREPEAT=""/>
<FIELD NAME="PLN" TYPE="TEXT" EMPTYOK="YES" MAXREPEAT=""/>
<FIELD NAME="CHF" TYPE="TEXT" EMPTYOK="YES" MAXREPEAT=""/>
</METADATA>

<RESULTSET FOUND="">

<xsl:for-each select="gesmes:Envelope/Cube">

<ROW MODID="" RECORDID="">

<COL><DATA><xsl:value-of select="time"/></DATA></COL>
<COL><DATA><xsl:value-of select=" "/></DATA></COL>
<COL><DATA><xsl:value-of select=" "/></DATA></COL>
<COL><DATA><xsl:value-of select=" "/></DATA></COL>
<COL><DATA><xsl:value-of select=" "/></DATA></COL>
<COL><DATA><xsl:value-of select=" "/></DATA></COL>
<COL><DATA><xsl:value-of select=" "/></DATA></COL>
<COL><DATA><xsl:value-of select=" "/></DATA></COL>

</ROW>

</xsl:for-each>

</RESULTSET>
</FMPXMLRESULT>

</xsl:template>
</xsl:stylesheet>

Can someone please help me to finish this XSL scheme?

I googled many related topics, but found no result that I can use for this...

How can we help you "finish" it, when you have given us no indication of what the finished article is supposed to do? — Michael Kay, Dec 27 '22 at 14:00
Please clarify your specific problem or provide additional details to highlight exactly what you need. As it's currently written, it's hard to tell exactly what you're asking. — Community, Dec 27 '22 at 17:57
Thank you, it is already done. I received the missing part of code and it works... — Andrej Macho, Dec 28 '22 at 16:46

michael.hor257k · Answer 1 · 2022-12-27T11:39:12.323

First thing: you need to declare the namespaces that the input XML uses, and apply them when addressing the input's elements - see: https://stackoverflow.com/a/34762628/3016153;

Next, you need to use the attribute axis when addressing attributes. And you need to use a predicate to select the element with the wanted attribute;

Finally, you don't need all that extra baggage that isn't used during XML import:

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"
exclude-result-prefixes="gesmes ecb">
<xsl:output method="xml" version="1.0" encoding="utf-8" indent="yes"/>

<xsl:template match="/gesmes:Envelope">
    <FMPXMLRESULT xmlns="http://www.filemaker.com/fmpxmlresult">
        <METADATA>
            <FIELD NAME="Date" TYPE="TEXT"/>
            <FIELD NAME="USD" TYPE="TEXT"/>
            <FIELD NAME="JPY" TYPE="TEXT"/>
            <FIELD NAME="CZK" TYPE="TEXT"/>
            <FIELD NAME="GBP" TYPE="TEXT"/>
            <FIELD NAME="HUF" TYPE="TEXT"/>
            <FIELD NAME="PLN" TYPE="TEXT"/>
            <FIELD NAME="CHF" TYPE="TEXT"/>
        </METADATA>
        <RESULTSET>
            <xsl:for-each select="ecb:Cube/ecb:Cube">
                <ROW>
                    <COL><DATA><xsl:value-of select="@time"/></DATA></COL>
                    <COL><DATA><xsl:value-of select="ecb:Cube[@currency='USD']/@rate"/></DATA></COL>
                    <COL><DATA><xsl:value-of select="ecb:Cube[@currency='JPY']/@rate"/></DATA></COL>
                    <COL><DATA><xsl:value-of select="ecb:Cube[@currency='CZK']/@rate"/></DATA></COL>
                    <COL><DATA><xsl:value-of select="ecb:Cube[@currency='GBP']/@rate"/></DATA></COL>
                    <COL><DATA><xsl:value-of select="ecb:Cube[@currency='HUF']/@rate"/></DATA></COL>
                    <COL><DATA><xsl:value-of select="ecb:Cube[@currency='PLN']/@rate"/></DATA></COL>
                    <COL><DATA><xsl:value-of select="ecb:Cube[@currency='CHF']/@rate"/></DATA></COL>
                </ROW>
            </xsl:for-each>
        </RESULTSET>
    </FMPXMLRESULT>
</xsl:template>

</xsl:stylesheet>

Thank you very much. I used it in my import script and works like a charm... — Andrej Macho, Dec 27 '22 at 12:32
Good. Unrelated to your question, but having 7 alike fields does not seem like the optimal structure. Consider having a record for each rate, with fields for Date, Currency and Rate. — michael.hor257k, Dec 27 '22 at 12:52

Creating XSL scheme for exchange rates

1 Answers1