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:
- Unable to deserialize XML element with namespaces
- Android Studio error "requires libraries and applications that depend on it to compile against version 33 or later of the Android APIs."
- Public myRibbon not stored long-term
- How to handle not always existing node while reading from XML
- xslt 1.0 - need a delimiter between each node value under for-each select
- Remove multiple XML elements
- Is Delphi XML binding really not allowing to read a ""-valued attribute?
- Amazon MWS submitting price setting feed
- error: style attribute 'android:attr/windowOptOutEdgeToEdgeEnforcement' not found
- xmlns=''> was not expected. - There is an error in XML document (2, 2)
- Get Orders data from Amazon MWS API using PHP
- Importing data from two XML parent nodes to a Pandas DataFrame using read_xml
- Issue with protection level when serializing
- Thread Safe XML Validation in Java 5
- Large XML files in dataset (outofmemory)
- Batch Replace and add 1 to a number between specific text in an xml file
- Azure APIm Caching throwing 500
- How can dynamic page numbers be inserted into added slides?
- PHP - convert XML to array with all attributes
- jquery parseXML giving invalid XML error
- I need a dotnet C# XML Serializer like XSerializer 0.1.28 for List<T> A and List<T> B elements serialized with the same name
- Can Jaxb marshal child elements without the root element?
- How do I change the separator from a space to a comma with xmlstarlet?
- Selecting multple values from an XML document in linux shell
- Material 3 MaterialSwitch size is too large by default? How to make it smaller?
- IOException parsing XML document from class path resource
- sp_xml_preparedocument: how to change SQL xml encoding
- JSON conversion to XML using attributes and namespaces
- JSON to XML Using Javascript
- Progress 4GL: Reading from XML to Dataset