Avoid these common glitches that may arise when attempting to transfer XML data into a MySQL table.
TL;DR: XML files generally have little in common with MySQL tables, which explains why importing data from an XML file to a MySQL table can be so troublesome. Here’s how to use MySQL’s LOAD XML command, stored procedures, and prepared statements to ensure all the XML data you want to import, and only the XML data you want to import, transfers safely and smoothly into your MySQL table.
Importing XML data into a MySQL database should be a piece of cake. Like many seemingly straightforward data-transfer operations, however, the process can be anything but cake-like.
Consider the common situation of needing to import only a select number of attributes from an XML file to a MySQL table, as presented in a Stack Overflow post from May 2014. The XML file contains team rosters that include information about each team’s players. Only select fields of player data are to be imported to the MySQL table: player_id (the primary key and autoincrement), first_name, last_name, and team.
MySQL’s LOAD XML command allows you to import only select attributes from an XML file into a MySQL table. Source: Stack Overflow
MySQL’s LOAD XML command works the same as LOAD DATA to import the XML attributes without an input value by assigning them to user variables, and by not assigning the variables to a table column.
The LOAD XML command imports XML attributes without an input value by using user-variable assignments. Source: Stack Overflow
In another Stack Overflow post from March 2011, import of an XML file to a MySQL table failed because the column count didn’t match the value count at row 1. The MySQL table had a field ID that wasn’t in the XML file. The goal was to import the XML file using MySQL queries in a way that bypasses the ID column and instead uses the autoincrement function for the ID column. The solution was to specify fields in the LOAD XML command:
By specifying fields in the LOAD XML command you can bypass the table’s ID column and use the autoincrement function in its place. Source: Stack Overflow
Use stored procedures and prepared statements to insert XML data
In a pair of articles from March and April 2014, Database Journal’s Rob Gravelle explains how to use a stored procedure to import XML data into a MySQL table; and how to enhance the process by using a Prepared Statement, and by adding error handling and validation.
Using stored procedures to import XML comes with limitations, as Gravelle points out. First, you haven’t been able to run LOAD XML INFILE and LOAD DATA INFILE statements in a stored procedure since MySQL 5.0.7. Also, you can’t make the procs very dynamic, so they can’t support a variety of file types. Finally, you can’t map XML data to table structures. However, if the XML file has a rigid and known structure, you can input the data with a single call.
Gravelle’s example XML file is a list of applicants, each of which has three attributes: ID, first name, and last name. The same three fields are in the target table, along with an int ID (the primary key) and two varchars.
Using a stored procedure to import an XML file to a MySQL table requires matching the file’s structure to the table beforehand. Source: Database Journal
MySQL’s Load_File() function is used to import the XML data into a local variable, and the ExtractValue() function is used to query the XML data using XPath. The row count lets you iterate over every XML row, since you’re not likely to know how many records will be imported with each run.
MySQL’s SQL interface for prepared statements can be accessed from within a stored procedure, although doing so is not as fast as using the binary protocol through a prepared statement API. Using the prepared-statement approach, you can compare the number of XML node attributes to the number of columns in the target table. If they don’t match, an error is displayed and the processing is paused.
The prepared statement lets you ensure the number of XML node attributes matches the number of columns in the target table. Source: Database Journal
The new Morpheus Virtual Appliance makes managing heterogeneous MySQL, MongoDB, Redis, and ElasticSearch databases a breeze. With the Morpheus database-as-a-service (DBaaS) you can provision, deploy, and monitor all your databases from a single point-and-click console. Morpheus lets you work with SQL, NoSQL, and in-memory databases across public, private, and hybrid clouds in just minutes. Each database instance you create includes a free full replica set for built-in fault tolerance and fail over.
In addition, the service allows you to migrate existing databases from a private cloud to the public cloud, or from public to private. A new instance of the same database type is created in the other cloud, and real-time replication keeps the two databases in sync. Visit the Morpheus site for pricing information and to create a free account.
