XML/XSD to Database using Java

[yatin naik]

Hi,
I have an XSD schema and associated XML file that i need to parse for all the elements and store it in our database. This XML will be updated on a daily basis.

I need suggestions on how to go about creating db tables from the XSD (if there are any tools available) and suggestions on the Java API to use for binding the XML data to the db tables. Since the XML file is very large, i don't want to write manual DOM code for parsing every element and inserting it in the database.

Thanks in advance....

[Marco Ehrentreich]

Hi Yatin,

welcome to the JavaRanch

It depends on the size of this project if it's worth the effort, but if your schema doesn't change too often one possible, more or less elegant solution could be to use JAXB which can help to create a complete Java object model to represent possible XML document in memory (but with typed classes for each node instead of generic DOM elements) and marshal/unmarshal to and from XML.

Additionally you could annotate this same class/object model to use JPA as O/R mapper which in turn can auto-generate the database schema from this object model an persist a unmarshalled XML document directly into a relational database.

If the schema changes you would have to step through the auto-generation process or modify the class model by hand. But on the other hand you would most probably have to do adapt your application with other solutions as well after a change to the schema.

Marco

[Velu Kasirajah]

You can also try Apache XMLBeans for parsing your XML data and creating Java objects. If shredding the data is not required, then you can also think about just dumping it as a BLOB.

[Marco Ehrentreich]

Writing the XML content or serialized object graph as a BLOB into the database would be the easiest solution of course. Just be aware that this could have negative effects on the application performance if the content is really big because you would always have to read/write the whole bunch of XML. Also you can't easily use database operations to query or modify the data inside the BLOB. This may not be an issue depending on your requirements, but take the time to think about it carefully.

Marco

[Velu Kasirajah]

Yes. If there's any need to query the XML data based on certain criteria, then do the required level of shredding before putting it into the database. This will make it easier to query and retrieve the data.