Importing XML into Excel using the Developer function?

Community Zone Using IATI Data
#1

Hello, I’m trying to import an IATI XML file straight into Excel, following the instructions here: https://support.office.com/en-us/article/Import-XML-data-6eca3906-d6c9-4f0d-b911-c736da817fa4?ui=en-US&rs=en-US&ad=US#bmimport_an_xml_file_as_an_xml_list_wit

First, I tried to import the iati-activities-schema.xsd to create a map (using Developer>Source, as per the instructions) but got an error message. Now I’m trying to import a relatively small XML file into Excel, but it keeps crashing.

Anyone with experience of doing this method? Or with using a XML to CSV convertor which will ‘unfold’ all the elements if you import the raw data?

Reflecting on … why couldn’t we just have asked people to upload data in a spreadsheet file: http://www.threesixtygiving.org/standard/ …

#2

In the blog post he published this week, Liam Swiss says it was really difficult to do xml-to-csv but that the Datastore made it much easier. http://blog.liamswiss.com/2016/02/09/iati-import-for-stata/

He might have more to share, I could try to connect you two on Twitter.

#3

I have had some fair success with ImportXML in Google Sheets on various XML.

The inherent problem though, is that IATI data is “3D” - it doesn’t translate easily into rows.

I can get all BOND transactions from one of BOND’s XML files like this: =ImportXML(“http://109.123.121.248/csviati/static/2015-10-22/1445522882.92.xml","//transaction”)

Here’s ImportXML trying to ingest all of it:

=ImportXML(“http://109.123.121.248/csviati/static/2015-10-22/1445522882.92.xml","//*”)

Good luck

Ben