ImportXML Google Spreadsheet Alexa Ranking Checker

Question

Attempting to parse Alexa ranking within Google Doc using =ImportXML. The following works and displays the Global rank:

=value(ImportXML(CONCATENATE("http://data.alexa.com/data?cli=10&dat=snbamz&url=",$A$4),"//@rank[1]"))

In theory the following should display the country ranking if it exists: (this doesn't work)

=ImportXML(CONCATENATE("http://data.alexa.com/data?cli=10&dat=snbamz&url=",$A$4),"//@rank[2]")

The above is the part that isn't working. It's not pulling the value of the second "rank" attribute.

I have also tried: (doesn't work)

=ImportXML(CONCATENATE("http://data.alexa.com/data?cli=10&dat=snbamz&url=",$A$4),"/country/@rank")

Help? What am I doing wrong?

Answer 1

Your second XPath query may never work. You're trying to query the second @rank attribute of an element, but there may only be one. If you want to select the second @rank attribute in the whole document, use (//@rank)[2].

Your alternative query does not work, as country is not the root element. Use //country/@rank instead. It might be reasonable to provide the full path step by step, to prevent fetching the wrong elements/attributes (if they occur somewhere else in the document):

/alexa/sd/country/@rank

Please be aware that principally XPath is case sensitive, although Google Spreadsheet seems to be not. Better use all caps here, as the XML input does.

Answer 2

Through trial and error, I arrived at the following solution, accurate as of 2014-02-17.

=ImportXML(CONCAT("http://data.alexa.com/data?cli=10&url=", "www.google.com"), "number(//COUNTRY[@code='US']/@rank)")

Observations:

• tags appear to be case-sensitive
• attributes must be lower-case