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