Using Becker's XPath method for more than the first element?

3
votes

I'm trying to parse through an XML file, using importXML, on a Google apps spreadsheet.

My problem is that I have my XML document set up like this:

staffList
    staff
        name
        email
    staff
        name
    staff
        name
        email

and I'm trying to select all of the email elements (I tried //staff/email, but it doesn't insert null if the node does not exist), and write NULL if the node for email does not exist (like in the above example).

I tried the XPath query below, but it only is selecting the first email element, and writing into 1 row, rather than writing into n rows, where n is the number of 'staff' nodes.

=ImportXML("http://www.engr.udel.edu/mf/test_staff.xml";"concat(/staffList/staff/email, substring('not-found', 1 div not(/staffList/staff/email[node()])))")

Does anybody have any ideas? Thanks in advance!

1
xmlxpathgoogle-sheetsgoogle-sheets-importxml

1 Answers

3
votes

Use:

//staff/email | //staff[not(email)]

This selects all existing email elements whose parent is a staff element ... plus any staff element that doesn't have an email child.

Not exactly as "returning a null" (because there is no such thing as "null" in XPath), but your code will need just to check for a staff element instead for a "null".