recommendationXPath seems valid but Google spreadsheet just gives "import internal error"

Question

I'm trying to use Google spreadsheet to return the @name attribute from a <row> element when @characterID has a certain value.

Google spreadsheet gives an "import internal error" when trying to use the following:

=IMPORTXML(CONCATENATE("http://api.eveonline.com/account/Characters.xml.aspx?keyID=",Summary!$B$10,"&vCode=",Summary!$B$11),"//eveapi/result/rowset/row[contains(@characterID,'94492798')]/@name")

The concatenate function provides a valid URL and http://www.freeformatter.com/xpath-tester.html gives me exactly what I want when I use

/eveapi/result/rowset/row[contains(@characterID,'94492798')]/@name

Any variations fail to work, too. eg //row[@characterID='94492798']/@name

Any ideas how I can get this to work?

Edit: FYI, it doesn't seem to like the predicate. Without the predicate, it returns all 3 rows with the @name attribute which is expected, but not what I'm looking to achieve.

Edit: At dirkk's recommendation, here's the relevant XML:

<?xml version='1.0' encoding='UTF-8'?>
<eveapi version="2">
<currentTime>2014-04-23 16:33:25</currentTime>
<result>
<rowset name="characters" key="characterID" columns="name,characterID,corporationName,corporationID,allianceID,allianceName,factionID,factionName">
<row name="Tor Norman" characterID="94488288" corporationName="Brave Newbies Inc." corporationID="98169165" allianceID="99003214" allianceName="Brave Collective" factionID="0" factionName="" />
<row name="Tor Dodi" characterID="94492798" corporationName="University of Caille" corporationID="1000115" allianceID="0" allianceName="" factionID="0" factionName="" />
<row name="Tor Jita" characterID="94506820" corporationName="School of Applied Knowledge" corporationID="1000044" allianceID="0" allianceName="" factionID="0" factionName="" />
</rowset>
</result>
<cachedUntil>2014-04-23 17:28:22</cachedUntil>
</eveapi>

Your XPath looks fine and the error message already indicates: That is a problem at Google, not your fault. Either they do not implement the whole XPath spec (which would be ridiculous as you use only very basic operators) or there is some bug. I would report it to the Google Docs people. — dirkk, Apr 23 '14 at 07:25
btw: Please always give data which we all can easily access. The URL is not accessible without some kind of api key and is therefore of no value to us. A small example data set would be nice. — dirkk, Apr 23 '14 at 07:26
It could be that their XPath engine doesn't support the `contains()` function. Do you really need the `contains()` function here? How about `/eveapi/result/rowset/row[@characterID ='94492798']/@name`? — JLRishe, Apr 23 '14 at 17:12
I don't need it, no. I've only attempted it as `/eveapi/result/rowset/row[@characterID='94492798']/@name` which, was my initial attempt failed. Incidentally, something like `//row[2]/@name` works just fine. The issue is definately in the predicate. — Mosseman, Apr 23 '14 at 21:45

score 3 · Answer 1 · answered Oct 28 '14 at 20:55

Google's implementation of XPath is flawed in that it does not recognize capital characters properly. Change the predicate "characterID" to "characterid" and it works fine.

Also, importXML frequently bugs out for no discernible reason. I've sent dozens of reports over the last year and still no fix.

Stay Brave. 7o

score 2 · Answer 2 · answered Oct 01 '14 at 00:18

2

Best workaround I found on this is to copy the sheet to a new sheet. For some reason it keeps working for a while until it starts bugging out as well. Alternative if you have a way to edit the used formula but get same result, then it works a while

answered Oct 01 '14 at 00:18

Taoh

331
2
12

score 0 · Answer 3 · answered Apr 24 '14 at 00:20

I've had a lot of issues with these "import internal error" messages. It changes randomly on cells that worked fine before. The only thing I can guess is that it's a Google issue. I haven't found an answer to it yet :( Just by the very nature of using 1 importXML statement, and dragging it down 166 rows, having some work and some not, at random times, tells me it's google. The randomness in how it works on all columns some times, 1/2 the columns other times, and only a few columns at other times, is clear it's NOT the statement.

score 0 · Answer 4 · answered Apr 24 '14 at 11:04

I did some quick testing with this, I found that as soon as there is the string 'ID' in the XPath expression google throws an internal error.

I got around your problem by using the following XPath:

//row[@*[contains(name(),'character')]='94488288']/@name

You can check out my tests here.

recommendationXPath seems valid but Google spreadsheet just gives "import internal error"

4 Answers4