Convert XML into a Dataset

Question

I'm trying to convert an XML document into a dataset that I can import into a database (like SQLite or MySQL) that I can query from.

It's an XML file that holds most of the stuff in attributes. This is part of a Rails project so I'm very inclined to use Ruby (and that's the language I'm most comfortable with at the moment).

I'm not sure how to go about doing that and I'd welcome both high-level and low-level contributions.

Answer 1

There are three basic approaches:

1. Use ruby's xml stream parsing facilities to process the data with ruby code and write the appropriate rows to the database.
2. Transform the xml using xslt to a non-XML stream format and feed that into a ruby program that updates the database
3. Transform the xml with xslt into a format acceptable to the bulk-loading tool for whatever database you are using.

Only you can determine the best approach depending on the XML schema complexity and the type of mapping you have to perform to get it into relational format.

It might help if you could post a sample of the XML and the DB schema you have to populate.

Answer 2

xmlsimple can convert your xml into a Ruby object (or nested object) which you can then look over and do whatever you like with. Makes working XML in Ruby really easy. As Jim says though depends on your XML complexity and your needs.

Answer 3

Will it load model data? If you're on *nix take a look at libxml-ruby. =)

With it you can load the XML, and iteration through the nodes you can create your AR objects.

Answer 4

You can have a look at the XMLMapping gem. It lets you define different classes depending upon the structure of your XML. Now you can create objects from those classes.

Now you will have to write some module which actually converts these XMLMapping objects into ActiveRecord objects. Once those are converted to AR objects you can simply call save to save those objects into the corresponding tables.

It is a long solution but it will let you create objects out of your XML without iterating over it. XMLMapping will do it for you.

Answer 5

Have you considered loading the data into an XML database?

Without knowing what the structure of the data is, I have no idea what the benefits of an RDBMS over an XML DB are.