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

Question

I need to get data from an XML file and store it into a MySQL Database. I am thinking of using a SAX Parser for parsing the data but I am not sure of how to store data efficiently into database, I am thinking of few technologies like JDBC and Hibernate but I wanted to ask about what would be the efficient way of doing it?

Note: Here programming language is Java.

Answer 1

I would suggest using JAXB for reading in the XML to Java objects and JPA for writing them into the database. You can create a single data model using Java classes that have both annotations for XML binding using JAXB and database persistence annotations using JPA.

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

Information on JAXB annotations. Information on JPA.

Answer 2

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

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

You probably have elements or attributes mappied to columns on DB. You can then query elements/attrs using XPath for simplicity and write them to DB. It this is a one-time conversion, I recommend using simple JDBC. Don't think for JPA or Hibernate, as it just increases your development time for a routine data conversion scenario.

Answer 3

You could use Castor witch is an open source data binding framework for moving data from XML to Java programming language objects and from Java to databases.

I found also article series in IBM developerWorks that describe using Castor suited to your needs.

Answer 4

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

Index XML documents with VTD-XML