importxml in googlesheets from formatted XML (not html)

Question

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.

E.Wiest · Accepted Answer · 2020-07-27 18:16:31Z

1

Use IMPORTDATA, QUERY and REGEXEXTRACT for this case :

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

Output :

answered Jul 27, 2020 at 18:16

E.Wiest

5,9152 gold badges9 silver badges12 bronze badges

Sign up to request clarification or add additional context in comments.

7 Comments

Guy Manova Over a year ago

done. yup worked like a charm and i learned a few things in the process, many thanks!

Andy Thompson Over a year ago

Unfortunately the use of REGEXEXTRACT turns any multi row result from QUERY into a single row answer. The answer is a neat trick for returning the top row but if you wanted the history of the data it doesn't work. I have yet to find an open source answer although there are paid solutions. You would think Google would expand the Google Visualization API Query Language to include some text wrangling regex functionality...

E.Wiest Over a year ago

Use ARRAYFORMULA with REGEXEXTRACT and you're just fine. =ARRAYFORMULA(REGEXEXTRACT(QUERY(IMPORTDATA(B4);"select Col1 WHERE Col1 starts with '<generic:ObsValue'");"\d\.\d+"))

Andy Thompson Over a year ago

Lol - literally just found that on waking up this morning (went to bed thinking about it and dreamt a few possibilities!!) then came here to add it and you beat me to it!!

Andy Thompson Over a year ago

Also, there appear to be three columns returned when you select * as per the OP; if you use select Col1 instead, it allows you to place similar arrays in contiguous columns without getting an overwrite error from Excel

|

Collectives™ on Stack Overflow

importxml in googlesheets from formatted XML (not html)

1 Answer 1

7 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

7 Comments

Your Answer

Sign up or log in

Post as a guest

Related