Using Google Spreasheet's IMPORTXML Function to Download Selected Multiple Columns

Question

I use Excel's XML mapping to download RSS feeds and select elements to display in column. I'm currently using Google Spreadsheet's IMPORTXML functions and trying to recreate my below Excel results.

The site being queried uses XML namespaces for its RSS feed and so [local-name() = "ELEMENTNAME"] is applied in the query. I'm using the following formula, where "URL" and "ELEMENTNAME" are defined ranges, for each of the eight columns:

=IMPORTXML(URL,CONCATENATE("//*[local-name() = '",*ELEMENTNAME*,"']"))

Without the CONCATENATE function the formulas are:

//*/[local-name() = 'filing-date']
//*/[local-name() = 'size']
etc.

The output is fine except (1) it requires multiple queries for a single URI that (I believe) may slow the workbook as more queries are added and (2) for columns that have null entries in certain rows, such as the last two on the right, the table displays them consecutively from the top-down, i.e. the column results are not in their correct rows.

I also tested using the below query with specific elements selected though the output displays in rows rather than columns and are not organized as listed in the query:

//*[local-name() = 'filing-date'] | //*[local-name() = 'filing-type'] | //*[local-name() = 'filing-href'] | //*[local-name() = 'form-name'] | //*[local-name() = 'size'] | //*[local-name() = 'amend']

Is there a way to consolidate these queries into one and, if the only solution is to keep the queries separate, have the null entries for each element appear as a blank cell?

Answer 1

It's not possible to achieve the desired result by using IMPORTXML alone, I think. Instead use Google Apps Script and the XML Service Service.

Example:

---

Adaptation of the code taken from the answer by Vidar S. Ramdal

---

function parseFeedXml(url) {
   var result = [];
   var xml = UrlFetchApp.fetch(url).getContentText();
   var document = XmlService.parse(xml);
   var atom = XmlService.getNamespace('http://www.w3.org/2005/Atom');
   var feed = document.getRootElement();
   var entry = feed.getChildren('entry',atom);
   for (var i = 0; i < entry.length; i++) { 
     var content = entry[i].getChildren('content',atom);
     var contentElement = '';
     var resultRow = [];
     for (var j = 0; j < content.length; j++) { 
       contentElement = content[j];
       evaluateText(contentElement.getChild('filing-date',atom), resultRow);
       evaluateText(contentElement.getChild('filing-type',atom), resultRow);
       evaluateText(contentElement.getChild('filing-href',atom), resultRow);
       evaluateText(contentElement.getChild('form-name',atom), resultRow);
       evaluateText(contentElement.getChild('size',atom), resultRow);
       evaluateText(contentElement.getChild('amend',atom), resultRow);
       result.push(resultRow);                
     }
   }
   return result;
}

function evaluateText(element, resultRow) {
  if (element) {
    resultRow.push(element.getText());
  } else {
    resultRow.push('');
  }
}