Extract XML with XPath Node

KNIME Analytics Platform
1

hello,

I like to extract the data of the following XML with X-Path. But I’m not able to get the right date (time) to every value.
The X-Path Node looks like the correct one, and currency and rate are collection. But sometimes time is missing then currency is several times in one line.
How do I extract the values like as follows?

2021-02-11 USD 1.2147
2021-02-11 JPY 127.12
2021-02-11 BGN 1.9558
…
2021-02-10 USD 1.2127
2021-02-10 JPY 127.04
2021-02-10BGN 1.9558

<?xml version="1.0" encoding="UTF-8"?>
<gesmes:Envelope xmlns="http://www.ecb.int/vocabulary/2002-08-01/eurofxref" xmlns:gesmes="http://www.gesmes.org/xml/2002-08-01">
    <gesmes:subject>Reference rates</gesmes:subject>
    <gesmes:Sender>
        <gesmes:name>European Central Bank</gesmes:name>
    </gesmes:Sender>
    <Cube>
        <Cube time="2021-02-11">
            <Cube currency="USD" rate="1.2147">
            </Cube>
            <Cube currency="JPY" rate="127.12">
            </Cube>
            <Cube currency="BGN" rate="1.9558">
            </Cube>
            <Cube currency="CZK" rate="25.772">
            </Cube>
            <Cube currency="DKK" rate="7.438">
            </Cube>
            <Cube currency="GBP" rate="0.87755">
            </Cube>
            <Cube currency="HUF" rate="357.18">
            </Cube>
            <Cube currency="PLN" rate="4.4975">
            </Cube>
            <Cube currency="RON" rate="4.8745">
            </Cube>
            <Cube currency="SEK" rate="10.0868">
            </Cube>
            <Cube currency="CHF" rate="1.0802">
            </Cube>
            <Cube currency="ISK" rate="155.8">
            </Cube>
            <Cube currency="NOK" rate="10.2595">
            </Cube>
            <Cube currency="HRK" rate="7.5688">
            </Cube>
            <Cube currency="RUB" rate="89.3792">
            </Cube>
            <Cube currency="TRY" rate="8.5254">
            </Cube>
            <Cube currency="AUD" rate="1.5638">
            </Cube>
            <Cube currency="BRL" rate="6.4936">
            </Cube>
            <Cube currency="CAD" rate="1.5384">
            </Cube>
            <Cube currency="CNY" rate="7.8448">
            </Cube>
            <Cube currency="HKD" rate="9.4171">
            </Cube>
            <Cube currency="IDR" rate="16963.1">
            </Cube>
            <Cube currency="ILS" rate="3.9531">
            </Cube>
            <Cube currency="INR" rate="88.404">
            </Cube>
            <Cube currency="KRW" rate="1339.97">
            </Cube>
            <Cube currency="MXN" rate="24.2037">
            </Cube>
            <Cube currency="MYR" rate="4.9104">
            </Cube>
            <Cube currency="NZD" rate="1.6772">
            </Cube>
            <Cube currency="PHP" rate="58.343">
            </Cube>
            <Cube currency="SGD" rate="1.608">
            </Cube>
            <Cube currency="THB" rate="36.271">
            </Cube>
            <Cube currency="ZAR" rate="17.7533">
            </Cube>
        </Cube>
        <Cube time="2021-02-10">
            <Cube currency="USD" rate="1.2127">
            </Cube>
            <Cube currency="JPY" rate="127.04">
            </Cube>
            <Cube currency="BGN" rate="1.9558">
            </Cube>
            <Cube currency="CZK" rate="25.835">
            </Cube>
            <Cube currency="DKK" rate="7.4373">

thanks and br,
sven

2

hello,

currently the result looks as follows:
image

BR,
Sven

3

I once did a Mini Workshop with this data, really nice one to show of timeseries prediction.

You can find my workflows here: iris/Public – KNIME_Workshop_ECB_Analytics – KNIME Hub

And with this one I did the crawling: ParseData – KNIME Hub

However, I did this some time ago, so you might need to do some cleanup with the deprecated nodes.

2 Likes
4

hello @Iris,

thanks for your reply. so it’s necessary to parse every single currency?

br,
Sven

5

hello @Iris,

i think it’s working now. instead of using several nodes i’m useing a loop.

BR,
Sven

6

Do you have a query or a page to get all of the conversion rates?
I did it like this because I only find the single conversion rates pages.

7

I would suggest using APIs for that. That should make thinks simpler.
BR

8

It is the API of the ECB we are using here. I don’t think we can ask them to change this :wink:

9

Oh ok, I thought something else because from my experience the API should normally return json and not xml

10

hello @Daniel_Weikert,

there are some apis “on the market” (free and paid) - I have a second thread here where I tried to use API and JSON, but the API was not able to deliver JSON conform data.

@Iris: I used this page: Euro foreign exchange reference rates
For my first version, but I was not working correctly.

BR,
Sven

11

This topic was automatically closed 182 days after the last reply. New replies are no longer allowed.