XML to Relational with DB2 and Java (and Hibernate?)

Question

What I have here is a bunch of XML-Files containing data and a nice ER-Model to which the data belongs. What my problem is: I need to get this data into a db2. The tables with all necessary attributes and keys are already created. I was thinking of three different solutions:

1. Parsing the XML and creating SQL-Queries from it. This solution seems straight forward but would require a lot of rather ugly string comparations and the like.

2. Inserting the XML into the database and levering pureXML to query the data from it and then inserting it into the relational tables. This would require some tinkering with pureXML or XML Extender as I'm not really familiar with them.

3. The IMHO most elegant solution. Creating an object model of the ER-model and a Hibernate mapping. Than use reflection to create objects from the XML data. Than somehow get the relations between the objects right (This isprobably the hardest part.) and let Hibernate to the rest.

Is there another way I have overlooked? What solution would you prefer? Is think there are a lot of pitfalls along the way with either solution and I'm afraid I completely missed something.

Answer 1

Number 3 all the way. You talk about using reflection to create the objects from the XML. have you looked at using jibx to map the xml to your object model.

What you just described is used a lot in STP systems to map xml from other sources into downstream systems.

http://jibx.sourceforge.net/

Karl

Answer 2

Creating an intermediate object model is a great approach. For this I recommend EclipseLink (TopLink was open sourced as EclipseLink). EclipseLink offers both JPA (object-to-database) and JAXB (object-to-XML) support.

EclipseLink JAXB (MOXy) offers extensions specifically for dealing with JPA entities:

• http://wiki.eclipse.org/EclipseLink/Examples/MOXy/JPA