Using Google Sheets with ImportXML to get data from website

user_10249921 0
Viewed 84

I am trying to get a list of entries from an XML file which is essentially an open data source.

EDIT:

If i use

=IMPORTXML(B1;”/*”)

all the content gets jammed into one cell. B1 is the field where the website URL is stored. What i would need in seperate columns are the title, the type, the city and the media objects. The url:

http://meta.et4.de/rest.ashx/search/?experience=open-data-niedersachsen-tourismus&licensekey=VdEVEni8FhWA58234fIbjwk0bAysCkHhvXXTnC5b&type=POI&latitude=52.3744779&longitude=9.7385532&distance=30000&template=ET2014A_LIGHT_MULTI.xml

Looking forward to any idea, hint and answer. Greetings

google-sheets-formula google-sheets
3 Answers

Using importxml and xpath:

The site uses a namespace, so use local-name()

//*[local-name() ='title']  
//*[local-name() ='type']   
//*[local-name() ='city']
//*[local-name() ='media_objects']

a complex one to combine title/cities/media_object (/.. allow to get parents)

//*[local-name() ='media_object']/../../*[local-name() ='title']|//*[local-name() ='media_object']/../../*[local-name() ='city']|//*[local-name() ='media_object']/@*[local-name() ='source']|//*[local-name() ='media_object']/@*[local-name() ='url']

enter image description here

For default media, use these xpathes

//*[@*[local-name() = 'rel']='default']/../../*[local-name() ='title']  
//*[@*[local-name() = 'rel']='default']/../../*[local-name() ='city']   
//*[@*[local-name() = 'rel']='default']/./@*[local-name() ='source']    
//*[@*[local-name() = 'rel']='default']/./@*[local-name() ='url']

enter image description here

user_150193800

If you want to get the title of that page, use:

=IMPORTXML(B1,"//title", "en_US")

Which shows - for your page:

POI

See here the Google Sheet example.

I also think your formula has wrong quotes, that might be the reason you got that error.

If this is not the "title" you're trying to get, please, edit your question and explain which "title" = the value - you want to get.

user_125118010

Using XmlService:

this is a completely different way, so I took the liberty of answering separately a second time

=parseXmlNamespace(A1)

with this script using namespace

function parseXmlNamespace(url) {
  var xml = UrlFetchApp.fetch(url).getContentText();
  var document = XmlService.parse(xml);
  var root = document.getRootElement();
  var nm = root.getNamespace();
  var result = [['title', 'city', 'media object', 'source', 'url']]
  var noeuds = root
    .getChildren('results', nm)[0]
    .getChildren('result', nm)[0]
    .getChildren('items', nm)[0]
    .getChildren('item', nm);
  noeuds.forEach(item => {
    try {
      item.getChildren('media_objects', nm)[0].getChildren('media_object', nm).forEach(it => {
        result.push([
          item.getChildren('title', nm)[0].getText(),
          item.getChildren('city', nm)[0].getText(),
          it.getText(),
          it.getAttribute('source').getValue(),
          it.getAttribute('url').getValue()])
      })
    } catch (e) {
      result.push([
        item.getChildren('title', nm)[0].getText(),
        item.getChildren('city', nm)[0].getText(),
        '', '', ''])
    }
  })
  return result
}

Class XmlService

enter image description here

user_150193800
Related