Excel: How does the =FILTERXML function work?

Question

I'm trying to get FX rate from ECB in an Excel Spreadsheet. First of all I get the XML:

=WEBSERVICE("https://sdw-wsrest.ecb.europa.eu/service/data/EXR/D.USD.EUR.SP00.A?lastNObservations=1")

Then I try to get the exchange with the FILTERXML function:

=FILTERXML(cell_with_XML,"//message:genericdata/message:dataset/generic:series/generic:obs/generic:obsvalue/@value")

However, I always get a #VALUE error. Can anybody explain me what I'm doing wrong?

Check this [article.](https://stackoverflow.com/a/61837697/5514747) — Harun24hr, Aug 06 '21 at 07:59

score 0 · Answer 1 · answered May 23 '22 at 15:14

You have to deal with xml namespaces. Remove them with SUBSTITUTE, then write your XPath expression to get the data from the attribute, not the node.

Working example :

=FILTERXML(SUBSTITUTE(WEBSERVICE("https://sdw-wsrest.ecb.europa.eu/service/data/EXR/D.USD.EUR.SP00.A?lastNObservations=1"),":",""),"//genericObsValue/@value")

Output :

Excel: How does the =FILTERXML function work?

1 Answers1