Using IMPORTXML in Google Sheets: How to Combine XML Data Sources as a Single Set Before Applying XPATH Query

Question

Using the IMPORTXML function on Google Sheets, I've got two XML data sets at two URLs, respectively in cells A1 and A2. I've got the XPATH query in cell H1. I would like to run a single query on the combined XML datasets (i.e., the combined result of XML data from URLs in A1 and A2.

As an example, [this URL][1] is in cell A1 and [this second URL][2] is in cell A2. The xpath in cell H1 is (//*[local-name() = 'NEW_DATE'])[last() - position() = 25]/../*[local-name()! = 'Id'].

I've tried the below formula, which returns two query results, one for each URL where the XML query in cell H1 contains [last() - position() = 11] or #N/A error for the first URL and the 25th row for the second URL where the XML query is as above, i.e., [last() - position() = 25]:

=QUERY({IMPORTXML(A1,H1);IMPORTXML(A2,H1)})

Note that in this sample, the first URL has 13 results as of the date of this question (hence retreiving row 11 when the XML query includes [last() - position() = 11] works but not when the XML query includes [last() - position() = 25] because there is no 25th row in that URL's XML data). Combining XML results of the first and second URLs, I would like to retrieve the 12th row in the second URL, which would be the 25th row if both URLs are combined.

This user's question is similar though the OP seems to run a common XPATH query on multiple, uncombined URL sources.

I would like to run a single query after the URLs are combined into a single dataset.

Is this feasible?

[1]: http://data.treasury.gov/feed.svc/DailyTreasuryYieldCurveRateData?$filter=year(NEW_DATE) eq 2022 [2]: http://data.treasury.gov/feed.svc/DailyTreasuryYieldCurveRateData?$filter=year(NEW_DATE) eq 2021

Answer 1

Combine the xml data by using an array

• Enter this formula in Cell A4
  ={IMPORTXML($B$1,$B$2);IMPORTXML($D$1,$D$2)}

• Then transpose every 14th set of rows using this formula
  =ArrayFormula(split(transpose(split(regexreplace(query(array_constrain(if(mod(row(A4:A), 14) = 4, "^", "|") & A4:A,max(row(A4:A) * (A4:A <> "")),1),,9^99), "^\^",), "^", true, false)), "|", true, false))

How to adapt the formula to your data

The formula (as written) assumes that the IMPORTXML data commences on row#4 but this may not be true in every case.

In order to adapt the formula to the location of your data, the key element is:

• if(mod(row(A4:A), 14) = 4, In this case, the mod of the first row of data (row#4) divided by 14 (the number of rows in each set of data) = 4.

To adapt the formula:

• identify the row number for the first row of IMPORTXML data array
• calculate the MOD where the dividend = the starting row#, and the divisor = 14
• substitute your calculated mod for '4' in the if statement.

---

Your screen should look something like this. This snapshot is cropped; the data for each day extends across to column P

Hat-tip: Ed on Google Support: Transpose from column to rows, every nth rows