importing from xml to Google Sheets using importxml
I would like to import into Google Sheets the ECB official USD/EUR conversion rates from this link:
https://www.ecb.europa.eu/stats/policy_and_exchange_rates/euro_reference_exchange_rates/html/usd.xml
But I can't figure out how to phrase the XPATH. I tried this for example:
=importxml("https://www.ecb.europa.eu/stats/policy_and_exchange_rates/euro_reference_exchange_rates/html/usd.xml", "/CompactData/DataSet/Series/Obs/@TIME_PERIOD")
But I always get "import content is empty". Would appreciate any help, many thanks ahead!
Although I'm not sure whether I could correctly understand your expected values, how about the following formula?
Sample formula:
=LET(VALUE_URL,"https://www.ecb.europa.eu/stats/policy_and_exchange_rates/euro_reference_exchange_rates/html/usd.xml",BASE_XPATH,"//*[local-name()='Obs']/",{IMPORTXML(VALUE_URL, BASE_XPATH&"@TIME_PERIOD"),IMPORTXML(VALUE_URL, BASE_XPATH&"@OBS_VALUE")})
In this case, the XPath of each value is
//*[local-name()='Obs']/@TIME_PERIODand//*[local-name()='Obs']/@OBS_VALUE.For example, when you want to retrieve the values of
@TIME_PERIOD, you can use the following formula.=IMPORTXML("https://www.ecb.europa.eu/stats/policy_and_exchange_rates/euro_reference_exchange_rates/html/usd.xml","//*[local-name()='Obs']/@TIME_PERIOD")
Testing:
- Extract specific keywords from XML file with bash script
- Python for xml parsing, how to track correct tree
- How to select ranges of siblings in XSLT?
- What does "xmlns" in XML mean?
- My app's language is not changing after changing the device language
- FAB on top of Bottom Navigation
- How would I add to add an icon to my RSS feed XML file
- sed to match pattern across a newline
- Add a line break in the label of customUI.xml
- How can I pretty print XML content from the command line?
- Android strings.xml can't escape double quote
- Is there a language/format that allows to define an API in XML?
- override default ImageMagick policy.xml
- How do I indent an xml file in vim?
- How to show the icon and progress when installing an app on iPhone / iPad using a plist file, correctly?
- Why does the content through HttpURLConnection getInputStream differ from the content through Linux command curl or wget?
- Dynamic XML parsing, data storage, and forms in c#
- Reading attribute values with XmlReader
- JAXB Context expects empty namespace no matter what
- Can we use AWS lambda as a layer on EC2?
- How to sort an xml by a nested child element text value using etree in Python
- Finding xml text content from tag name in python
- Public myRibbon not stored long-term
- Add XML node dynamically with groovy
- XSD for simplecontent with attribute and text
- Generating an object model in Ruby from an XML DTD
- What are the official XML reserved characters?
- Access nested data api objects in react native
- JavaFx TableView Columns don't fill the TableView Width
- XSLT - Merge One Header with One LineItem to One Header Multiple Line Items