How to read xml data from a url and parse it ?

Technical support and scripting issues

Moderators: Dorian (MJT support), JRL

Post Reply
yadhukrishna36
Newbie
Posts: 13
Joined: Tue Oct 06, 2020 6:28 pm

How to read xml data from a url and parse it ?

Post by yadhukrishna36 » Tue Dec 29, 2020 12:31 pm

I need to get the currency exchange rates from a URL http://www.ecb.europa.eu/stats/eurofxre ... -daily.xml which gives an XML response, I tried with httpRequest but did not work as expected. Can somebody help me?

User avatar
Dorian (MJT support)
Automation Wizard
Posts: 1348
Joined: Sun Nov 03, 2002 3:19 am
Contact:

Re: How to read xml data from a url and parse it ?

Post by Dorian (MJT support) » Tue Dec 29, 2020 4:21 pm

I was able to extract this using Marcus' Convert XML to CSV example.

Code: Select all

//Be sure to edit file paths
HTTPRequest>https://www.ecb.europa.eu/stats/eurofxref/eurofxref-daily.xml,d:\XMLfile.xml,GET,,TheXML,,,,

VBSTART
Sub ConvertXMLtoCSV(xmlFile,csvFile)
    Set xlApp = CreateObject("Excel.Application")
    Set xlBook = xlApp.Workbooks.OpenXML(xmlFile,2)
    xlBook.SaveAs csvFile, 6
    xlBook.close false
    xlApp.quit
End Sub
VBEND

//Be sure to edit file paths
VBRun>ConvertXMLtoCSV,d:\XMLfile.xml,d:\convertedXML.csv
I couldn't get XMLParse to work on this, but I know we have a lot of XML people far more experienced than me, so maybe they can chime in. My workaround works though.
Yes, we have a Custom Scripting Service. Message me or go here

User avatar
Marcus Tettmar
Site Admin
Posts: 7378
Joined: Thu Sep 19, 2002 3:00 pm
Location: Dorset, UK
Contact:

Re: How to read xml data from a url and parse it ?

Post by Marcus Tettmar » Tue Dec 29, 2020 5:35 pm

Seems the outer stylesheet bits confuse it. But we can strip out just the bit we need (between <Cube> ... </Cube>) and work against that. E.g. this gets the GBP rate:

Code: Select all

HTTPRequest>https://www.ecb.europa.eu/stats/eurofxref/eurofxref-daily.xml,,GET,,TheXML,,,,
//remove all but the <Cube>..</Cube> bit
RegEx><Cube>.*<\/Cube>,TheXML,,matches,nm,0
Let>TheXML=matches_1

Let>expr=//Cube[@currency='GBP']//@rate
XMLParse>TheXML,expr,gbpRate,num
Marcus Tettmar
http://mjtnet.com/blog/ | http://twitter.com/marcustettmar

Did you know we are now offering affordable monthly subscriptions for Macro Scheduler Standard?

yadhukrishna36
Newbie
Posts: 13
Joined: Tue Oct 06, 2020 6:28 pm

Re: How to read xml data from a url and parse it ?

Post by yadhukrishna36 » Mon Jan 04, 2021 6:41 am

Thanks Marcus Tettmar :lol: :D

Post Reply
Sign up to our newsletter for free automation tips, tricks & discounts