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

[yadhukrishna36]

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?

[Dorian (MJT support)]

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.

[Marcus Tettmar]

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

[yadhukrishna36]

Thanks Marcus Tettmar