CODEWITHSUNDEEP
xmlxpathgoogle-sheets-formula
Guy Manova
Guy Manova

Reputation: 474

importxml in googlesheets from formatted XML (not html)

i'm trying to obtain a USD/EUR rate from the ECB site. it comes in XML, however when i use what i assume should be the xpath it yields an empty result and #N/A in googlesheets. obviously i'm defining the xpath wrong but i've tried several things and it's always empty:

here's the actual data api URL:

https://sdw-wsrest.ecb.europa.eu/service/data/EXR/D.USD.EUR.SP00.A?startPeriod=2020-06-30&endPeriod=2020-06-30

when inputting this + xpath into importxml() i tried this:

=IMPORTXML("https://sdw-wsrest.ecb.europa.eu/service/data/EXR/D.USD.EUR.SP00.A?startPeriod=2020-06-30&endPeriod=2020-06-30", "/generic:Obs/generic:ObsValue[1]")

no go.

Upvotes: 0

Views: 181

Answers (1)

E.Wiest
E.Wiest

Reputation: 5905

Use IMPORTDATA, QUERY and REGEXEXTRACT for this case :

=REGEXEXTRACT(QUERY(IMPORTDATA(B4);"select * WHERE Col1 starts with '<generic:ObsValue'");"=.(\d.\d+)")

Output :

USDEU

Upvotes: 1

Related Questions