Google Sheets XPath Query for XML namespace [duplicate]

Question

I'm trying to retrieve the last and second to last entries for the date element in the below example:

<c:classroom>Classroom 1</c:classroom>
    <a:date>2018-03-01</a:date>
    <a:teacher>Ms Smith</a:teacher>
    <a:assistant>Sara</a:assistant>
    <a:student>Rose</a:student>
    <a:student>Kris</a:student>
    <a:student>Jane</a:student>
<c:classroom>Classroom 2</c:classroom>
    <a:date>2018-03-02</a:date>
    <a:teacher>Mr Jones</a:teacher>
    <a:assistant>Bob</a:assistant>
    <a:student>Mike</a:student>
    <a:student>Pat</a:student>
    <a:student>Rick</a:student>
<c:classroom>Classroom 1</c:classroom>
    <a:date>2018-03-05</a:date>
    <a:teacher>Ms Smith</a:teacher>
    <a:assistant>Sara</a:assistant>
    <a:student>Rose</a:student>
    <a:student>Kris</a:student>
    <a:student>Jane</a:student>
<c:classroom>Classroom 2</c:classroom>
    <a:date>2018-03-06</a:date>
    <a:teacher>Mr Jones</a:teacher>
    <a:assistant>Bob</a:assistant>
    <a:student>Mike</a:student>
    <a:student>Pat</a:student>
    <a:student>Rick</a:student>

Using //*[local-name() = 'date'][last()] returns almost everything and using //*[local-name() = 'date'][last()-1] fails.

Any suggestions?

UPDATE: This XPath query is for use in Google Sheets.

This question should not be a duplicate as the linked references (which are very helpful) had been reviewed and Google Sheets doesn't accept all XQueries. This was originally tagged with google-spreadsheet and edited to explicitly state so. Also, it seems neither reference uses a combination of last() and position() operators as @zx485 used. Happy though to delete if SE still concludes it's a duplicate or doesn't contribute enough to stand alone. — Catalyx
– Catalyx, Commented Mar 6, 2018 at 21:46

zx485 · Accepted Answer · 2018-03-05 20:18:00Z

2

Try this XPath

(//*[local-name() = 'date'])[last() - position() &lt; 2]

This should get the last two dates in the document.

edited Mar 5, 2018 at 20:18

answered Mar 5, 2018 at 19:36

zx485

29.1k28 gold badges55 silver badges65 bronze badges

Sign up to request clarification or add additional context in comments.

3 Comments

zx485 Over a year ago

Thanks. The link was an interesting read.

Catalyx Over a year ago

Thanks for this. This question is actually not a duplicate as the references (which are very helpful) had been reviewed and Google Sheets doesn't accept all XQueries; e.g. Google Sheets doesn't seem compatible with < though < works. Could you point out how to get a line break for between the dates?

zx485 Over a year ago

The general approach is to concat() a 
 at the end of each node value. But I don't know what the google way of doing this is.

Collectives™ on Stack Overflow

Google Sheets XPath Query for XML namespace [duplicate]

1 Answer 1

3 Comments

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

3 Comments

Linked

Related