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 atom = XmlService.getNamespace('http://www.w3.org/2005/Atom');
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.