CODEWITHSUNDEEP
xmlxpathgoogle-sheets-formula
Pogz
Pogz

Reputation: 11

Is Google Sheet's XPath Query Following a Certain Standard?

I'm trying to create a list of stocks (PH stocks) in Google Spreadsheet wherein I have a column containing the latest price and compare it with my set target price. I have been trying to parse this http://phisix-api.appspot.com/stocks/BPI.xml this afternoon and I have been unable to get the values inside the tag.

Reading through the XPath documentation, I thought it was only as simple as: =IMPORTXML("http://phisix-api.appspot.com/stocks/BPI.xml", "//stocks/stock/price/amount") following the hierarchy of the tree and tracing the tag I need. But it doesn't seem to work. Then I tried =IMPORTXML("http://phisix-api.appspot.com/stocks/BPI.xml", "//stocks/stock/price/amount/text()") since what I need is the text inside the tag anyway, but no dice.

Is there a special encoding/standard or format which Google Sheets follow? The only thing I got to work is getting the value of the symbol by using =IMPORTXML("http://phisix-api.appspot.com/stocks/BPI.xml", "//@symbol") nothing else I try works.

I even tried the generated Xpath from https://xmltoolbox.appspot.com/xpath_generator.html which is: /stocks[@xmlns="http://phisix-api.appspot.com/phisix-stocks"]/stock[@symbol="BPI"]/price/amount but it doesn't work.

Any recommendations?

Upvotes: 1

Views: 40

Answers (1)

Yitzhak Khabinsky
Yitzhak Khabinsky

Reputation: 22187

The XML in question contains a default namespace. It creates a 'problem' for XPath expressions.

Google Sheets (GS) seems to be compatible with XPath 1.0 only .

If your environment supports XPath 2.0, it is possible to use namespace wildcard as follows.

XPath 2.0

/*:stocks/*:stock/*:price/*:amount/text()

XPath 1.0

/*[local-name()='stocks']/*[local-name()='stock']/*[local-name()='price']/*[local-name()='amount']/text()

Upvotes: 1

Related Questions