Extracting data from XML into SQL via Python

Question

Hi everyone,

  Background:  I've taken a spreadsheet of an org hierarchy and converted it to XML to import into yEd.  My plan is to give this yEd file to a client so that they can manually update the linkages (which are currently messy) to generate a proper Org chart of the company. Once that is done I want to extract those linkages back out, upload into SQL to update the back end data.

I'm planning to use Python to extract the information out of the XML (because I want to learn Python) but I'm open to suggestions.  Has anyone had any experience of this, any example code?

Thanks

Chris.

Answer 1

I use Python to extract data out of yEd output files (.graphml format) and populate an SQLite database.  I use the description fields in the yEd elements to hold additional data in the diagram, but you could use the labels as well depending on what your client is editing.  Each node and edge has a unique id so the connectivity of the graph is captured in the database table along with the data.

There is good support for reading and writing XML built into the standard Python distribution, but I've found the NetworkX Python library http://networkx.github.io/ to be particularly easy to use so you might want to download that as well.