CODEWITHSUNDEEP
xmlxpathgoogle-sheetsapi-eveonline
Mosseman
Mosseman

Reputation: 55

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

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>

Upvotes: 3

Views: 5538

Answers (4)

Yu Wish
Yu Wish

Reputation: 31

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

Upvotes: 3

Taoh
Taoh

Reputation: 331

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

Upvotes: 2

Tobias Klevenz
Tobias Klevenz

Reputation: 1645

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.

Upvotes: 0

TownSaver
TownSaver

Reputation: 1

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.

Upvotes: 0

Related Questions