XPath Query to Retrieve Element Value and Name

Question

Using the IMPORTXML function in Google Sheets, I'm trying to get the 'properties' element values from this link to appear with their element names as column headers.

Using the below XPath query outside Google Sheets, I'm able to get the data to display the element names and their values in rows separated by a space. In Google Sheets, I get an error (Imported Xml content can not be parsed.).

//*[local-name() = 'properties']/*[not(*)]/concat(local-name(), ' ', .)

I have a sample spreadsheet here with the desired output. Cell B12 contains the IMPORTXML formula that refers to the XPath in cell B4 (labeled as XPATH Query 2). Cell B3 ("XPATH Query 1") contains the query that seems to work outside of Google Sheets.

The below image displays the current output of cell B12 outlined in red and the desired output beginning in cell D11.

Answer 1

Alternatives

Here are alternative approaches to get the data in your preferred layout:

1. IMPORTDATA
   =IMPORTDATA(url, delimiter)
   Imports from a URL in CSV or TSV format.

   =LET(url, "https://home.treasury.gov/resource-center/data-chart-center/interest-rates/daily-treasury-rates.csv/2022/all?type=daily_treasury_yield_curve&field_tdr_date_value=2022&page&_format=csv",
        IMPORTDATA(url, ","))
   

2. IMPORTHTML
   =IMPORTHTML(url, query, index)
   Imports from a table or list within an HTML page.

   =LET(url, "https://home.treasury.gov/resource-center/data-chart-center/interest-rates/TextView?type=daily_treasury_yield_curve&field_tdr_date_value=2022",
        table, IMPORTHTML(url, "table"),
        CHOOSECOLS(table, 1, SEQUENCE(1,13,11)))
   

   • CHOOSECOLS is used to remove 9 columns from the array that were hidden columns in the source table and are not required.
   • Column 1 (NEW_DATE) is manually entered;
   • The remain columns are specified by generating a 13 number SEQUENCE from 11 to 23

Intermittent Connection Issues

I also encountered intermittent periods during testing where the URL would stop resolving.

This affected IMPORTXML and IMPORTHTML but I didn't witness it with IMPORTDATA. This may highlight a difference or I may simply have been lucky with IMPORTDATA and it would have behaved similarly eventually.

Modifying the URL to point to a different year then changing it back seemed to resolve the issue when it showed up.

Answer 2

Things to have in mind about using xPath on IMPORTXML

1. It supports xPath 1.0. An old version of the IMPORTXML help article mentioned the supported version; the current version points to a third-party tutorial about xPath hosted on https://www.w3schools.com/ that used to be unreliable. I don't know if they have improved or how Google has decided to point to that site. All of the xPath testers I found searching the Web support xPath 2.0.
2. Some time ago, I read that IMPORTXML can't return xPath function results because the above doesn't support xPath 2.0. I don't have a source to support this, but I haven't found examples that prove the contrary either.

Considering the above, and due to the difficulty of controlling the recalculation of spreadsheet formulas in Google Sheets, I lean to use Google Apps Script, the Url Fetch Service and the XML service.

One option might be to create a custom function. This might work similarly to IMPORTXML but might include a custom retry algorithm while the execution time does not exceed 30 seconds. If a longer execution time is required, the function could be triggered by an installable trigger, a custom menu, and other options.