How to read data from an XML file and store it in a database (MySQL)?

Question

I need to get data from an XML file and save it in a MySQL database. I am thinking of using SAX Parser to analyze data, but I'm not sure how to store data in a database efficiently, I am thinking of several technologies like JDBC and Hibernate , but I wanted to ask what would be an efficient way to do this?

Note. The programming language here is Java.

Answer 1

You can use Castor witch, an open source data binding framework for moving data from XML objects in the Java programming language and from Java to the database.

I also found a series of articles on IBM developerWorks that describe how to use Castor to suit your needs.

Answer 2

I would suggest using JAXB to read in XML objects for Java and JPA to write them to the database. You can create a single data model using Java classes that have annotations for binding XML using JAXB and annotations to save the database using JPA.

@Entity @Table(name="Foo") @XmlRootElement public class Foo { // ... } 

JAXB annotation information. Information about JPA .

Answer 3

It depends on many factors. If your XML is too large (> 1 GB or comparable to shared memory) then you should use SAX and I don't think there would be other solutions. If it's small (say, less than 100 MB), just load all the XML into the Document object using JAXP:

 DocumentBuilderFactory documentBuilderFactory = DocumentBuilderFactory.newInstance(); DocumentBuilder parser = documentBuilderFactory.newDocumentBuilder(); Document doc = parser.parse(source); 

You probably have elements or attributes that appear in database columns. You can then query / attrs with XPath for simplicity and write them to the database. This is a one-time conversion, I recommend using a simple JDBC. Do not think about JPA or Hibernate, as this simply increases development time for a typical data conversion scenario.

Answer 4

You can store XML in mySQL directly using blob ... if you want efficient indexing and high performance, VTD-XML has a built-in ability to index / query / update an XML document, which makes it a better alternative to SAX and DOM, here is a link to the related article

XML index documents with VTD-XML