Solved: Parsing XML response from HTTP Request

Parsing XML response from HTTP Request

Hi all,

Long time user, first time poster, as on this occasion I just can't seem to get it working.

Hopefully it's an easy solution and I'm just overlooking something simple. I've not really had any experience with XML before today.

I have created a cloud flow that calls the HMRC Exchanges Rates website via a HTTP GET request. Calling the API works fine

The flow just repeatedly calls the API, incrementing each time, starting in May 2019 to the present month and year then stops.

This is an example of one of the URIs: http://www.hmrc.gov.uk/softwaredevelopers/rates/exrates-monthly-0623.XML

The result is 50 calls with an XML body for each call.

The bodies of each call are the same, with the only part changing is the period for which it covers:

<exchangeRateMonthList Period="01/Jun/2023 to 30/Jun/2023">
<exchangeRate>
<countryName>Argentina</countryName>
<countryCode>AR</countryCode>
<currencyName>Peso </currencyName>
<currencyCode>ARS</currencyCode>
<rateNew>291.3043</rateNew>
</exchangeRate>
<exchangeRate>
<countryName>Australia</countryName>
<countryCode>AU</countryCode>
<currencyName>Dollar </currencyName>
<currencyCode>AUD</currencyCode>
<rateNew>1.8915</rateNew>
</exchangeRate>
<exchangeRate>
<countryName>Brazil</countryName>
<countryCode>BR</countryCode>
<currencyName>Real </currencyName>
<currencyCode>BRL</currencyCode>
<rateNew>6.1256</rateNew>
</exchangeRate>
</exchangeRateMonthList>

It's a very long list so I've only given the first two countries by way of an example of the XML structure.

The issue I'm having is parsing each XML response for importing into a SQL table. I've read countless sources from other users having similar issues or tutorials on how to do it, but I'm either hit with an error or not getting the outcome I'm after.

The below screenshot was from using:

xpath(xml(body('HTTP')),'/*[name()="exchangeRateMonthList"]/exchangeRate/countryName')

The output is usually the above, a long continuous string or I was able to successfully pull out the CountryNames, but only the first countryname from the list, not all of them.

What I'm ideally trying to achieve is that for each call is to be able to write the following variables to a sql table:

Period
countryName
countryCode
currencyName
currencyCode
rateNew

So I end up with a table full of historic exchange rates, for each country, with a field to identify which period it covers, hence the need to include the period field from the XML.

I hope this is sufficient information and I fully appreciate any advice in advance.

Best regards,

Cdaw

Quick Links

Leaderboard > Power Automate