Load XML Into MySQL Using Java

“Never memorize something that you can look up.”
― Albert Einstein

XML provides the ability to represent hierarchical structures with its parent-child relationships. This enables applications to store structured data in XML for export. Importing this XML data into a database is a bit involved as we shall see in this article. You need to write code to manage the database connection. In addition, you need to parse the XML and isolate the data that needs to be imported.

In this article, we show how to import and load XML data into a MySQL database. The application creates the table for storing the data and proceeds to import the data.

We are attempting to import data from books.xml, which looks like this:

<?xml version="1.0"?>
<catalog>
  <book id="bk101">
    <author>Gambardella, Matthew</author>
    <title>XML Developer's Guide</title>
    <genre>Computer</genre>
    <price>44.95</price>
    <publish_date>2000-10-01</publish_date>
    <description>An in-depth look at creating applications
    with XML.</description>
  </book>
  <book id="bk102">
    <author>Ralls, Kim</author>
    <title>Midnight Rain</title>
    <genre>Fantasy</genre>
...

2. Load MySQL JDBC Driver

Download the MySQL Connector/J Driver from here. Unpack the distribution and copy the mysql-connector-java-<version>-bin.jar to your application directory. (Replace <version> with the version of the driver you downloaded.)

The first thing we need to do in the application is to load the MySQL JDBC driver. While we use a static block for that, you can always load the driver while the application is running.

static {
    try { Class.forName("com.mysql.jdbc.Driver"); }
    catch(ClassNotFoundException ex) {
    System.err.println("Driver not found: " + ex.getMessage());
    }
};

3. Connect to MySQL

The format of the JDBC connection string is as shown. This string is used for connecting to a MySQL database running on the localhost — the database name we are using is testing. The username and password are also specified in the connection string.

String dbUrl="jdbc:mysql://localhost/testing?user=uname&password=pwd";

Here is the code to open a connection to the MySQL database using the above connection string.

Connection conn = DriverManager.getConnection(dbUrl);

If you would rather specify the username and password separately instead of including it in the connection string, you can do this instead. (Maybe you are obtaining these values from different locations.)

String dbUrl = "jdbc:mysql://localhost/testing";
String uname = ...;
String pwd = ...;
Connection conn = DriverManager.getConnection(dbUrl, uname, pwd);

4. Create MySQL Table

Let us now create the table with the structure needed for the storage.

conn.createStatement()
    .execute("CREATE TABLE books(\n" +
         " id integer primary key auto_increment,\n" +
         " book_id varchar(25) not null unique,\n" +
         " author varchar(50) not null,\n" +
         " title varchar(250) not null,\n" +
         " genre varchar(25) not null,\n" +
         " price float not null,\n" +
         " publish_date date not null,\n" +
         " description text not null\n" +
         ")");

Note the following:

• MySQL sets the id field to an auto incremented value when creating the row.
• We store the XML attribute id in the book_id field.
• The description field is text since we expect it to be somewhat large but not exceed 65,535 characters.

5. Parse XML

With the database setup code out of the way, let us look into importing the XML data into the application. See here for a tutorial about using an XML parser to parse the XML data.

File file = new File(fileName);
DocumentBuilderFactory factory = DocumentBuilderFactory.newInstance();
DocumentBuilder builder = factory.newDocumentBuilder();
Document xmlDoc = builder.parse(file);

With this code, we end up with a Document object representing the XML data in memory. We can use this Document object to search for the data we want to insert into the database.

To insert the data, we need to obtain the required data from the XML nodes. The following is a convenience method to extract an attribute value from a Node.

static private String getAttrValue(Node node,String attrName) {
    if ( ! node.hasAttributes() ) return "";
    NamedNodeMap nmap = node.getAttributes();
    if ( nmap == null ) return "";
    Node n = nmap.getNamedItem(attrName);
    if ( n == null ) return "";
    return n.getNodeValue();
}

And we use this convenience method to extract the text content of a named child element.

static private String getTextContent(Node parentNode,String childName) {
    NodeList nlist = parentNode.getChildNodes();
    for (int i = 0 ; i < nlist.getLength() ; i++) {
    Node n = nlist.item(i);
    String name = n.getNodeName();
    if ( name != null && name.equals(childName) )
        return n.getTextContent();
    }
    return "";
}

7. Prepare XML Data

We use XPath to extract the set of nodes whose data we want to insert into the database. Given the structure of the XML, the set of nodes are located at the XPath /catalog/book.

XPath xpath = XPathFactory.newInstance().newXPath();
Object res = xpath.evaluate("/catalog/book",
                 xmlDoc,
                 XPathConstants.NODESET);

The above code returns a NodeList that matched the specified XPath.

8. Insert into MySQL

To insert the data extracted in a loop, we create a PreparedStatement as follows:

PreparedStatement stmt = conn
    .prepareStatement("INSERT INTO books(\n" +
              " book_id, author, title, genre, price,\n" +
                 " publish_date, description)\n" +
                  "VALUES(?, ?, ?, ?, ?,\n" +
                  " str_to_date(?, '%Y-%m-%d'), ?)");

We use the following loop to extract and insert the data from the child elements using the convenience methods defined earlier:

for (int i = 0 ; i < nlist.getLength() ; i++) {
    Node node = nlist.item(i);
    List<String> columns = Arrays
    .asList(getAttrValue(node, "id"),
        getTextContent(node, "author"),
        getTextContent(node, "title"),
        getTextContent(node, "genre"),
        getTextContent(node, "price"),
        getTextContent(node, "publish_date"),
        getTextContent(node, "description"));
    for (int n = 0 ; n < columns.size() ; n++) {
    stmt.setString(n+1, columns.get(n));
    }
    stmt.execute();
}

With that, we have successfully imported data from an XML file into a MySQL database.

Summary

This article covered the details of importing XML into a MySQL database. We parsed the XML using the DOM parser. Then we used XPath to extract just the data we needed. Finally, we inserted it into the database using JDBC.