Google-sheets – Pull information about each game from boardgamegeek.com

As stated in my comment, doing this with =importXML and XPath seems tricky.

But doing it programmatically, using the XmlService of Google Apps Script, looks easier.

I have written the following:

function parseTvRageXml(url) {
   var result = [];
   var xml = UrlFetchApp.fetch(url).getContentText();
   var document = XmlService.parse(xml);
   var root = document.getRootElement();

   var show = document.getRootElement();
   var episodeList = show.getChild("Episodelist");
   var seasons = episodeList.getChildren("Season");
   for (var i = 0; i < seasons.length; i++) {
     var season = seasons[i];
     var seasonNum = season.getAttribute("no").getValue();
     var episodes = season.getChildren("episode");
     for (var j = 0; j < episodes.length; j++) {
       var episode = episodes[j];
       var resultRow = [];
       resultRow.push(seasonNum);
       resultRow.push(episode.getChild("epnum").getText());
       resultRow.push(episode.getChild("seasonnum").getText());
       resultRow.push(episode.getChild("airdate").getText());
       resultRow.push(episode.getChild("link").getText());
       resultRow.push(episode.getChild("title").getText());
       result.push(resultRow);                
     }
   }
  return result;
 }

In effect, it fetches the content from the given URL, parses it as XML, and iterates through the elements, extracting values of the elements you wanted, which is stored in a 2-dimensional array (result), and returned.

To use this, you must install the script in your spreadsheet. Click the Tools menu → Script editor, and paste the code listed above. Save the script.

In the cell where you want the output, enter

=parseTvRageXml("http://services.tvrage.com/feeds/episode_list.php?sid=3183")

The result should be a list of episodes, with the following columns:

Season number | epnum | seasonnum | airdate | link | title

I have set up an example spreadsheet to demonstrate this, feel free to copy it.

The command IMPORTXML ignores the nodes with no text content: for example, taking //div[3] from the document where the body consists of

<div>First</div> <div>Second</div> <div></div> <div>Fourth</div>

results in "Fourth". So, when you count the <div> elements, skip over those where there is no text. The elements you are looking for are returned with

=IMPORTXML( url , "//div[@id='companyProfile']/div[4]/span")

and

=IMPORTXML( url , "//div[@id='companyProfile']/div[5]/span")

For a more robust solution, I advise not relying on the numbering of elements at all. The following command returns both values you want with a single call, one under another:

=IMPORTXML( url , "//div[@id='companyProfile']/div[@class='sub-heading']/span")

You can apply TRANSPOSE to the result to put them side by side, if preferable. Or, if some custom positioning is needed, get one at a time with

=IMPORTXML( url , "//div[@id='companyProfile']/div[@class='sub-heading'][1]/span")

and

=IMPORTXML( url , "//div[@id='companyProfile']/div[@class='sub-heading'][2]/span")

Note that one should use single quotes in an XPath command, since it's a string surrounded by double quotes.

Using Inspect Element tool is not an inherently bad idea (it shows a nice tree view of the document), but there is an important caveat: this tool shows the document after any JavaScript runs on the page, while IMPORTXML gets the source as it is before any JavaScript processing. This matters when some elements get added by a script (example in my answer here). To see exactly what IMPORTXML works with, use right-click -> "View Page Source" in Chrome, or its equivalent in other browsers.