Googlesheets XML Import from Bank of Canada

Question

I am trying to get specific exchange rates for a given interval from the Bank of Canada I am not very familiar with XLM importing but all the videos and examples on the internet seem to be very different compared to this)

I am using something like this to get the values

=importxml("https://www.bankofcanada.ca/valet/observations/FXCADUSD/xml?start_date=2021-02-01&end_date=2021-05-01","//o")

I tried different XPath alternatives but I was not able to get the dates that correspond to the values. I am trying to get the dates and values side by side.

Any help is much appreciated. Thanks

Answer 1

The dates are an attribute of the d elements, so to get them you have to change your xpath expression from

//o

to

//o/@d

to get both dates and values (in one column), change the expression to

//o/@d | //o/v

Google Sheets supports only xpath 1.0, unfortunately, so more effective xpath expressions aren't available.