Data Formatting, Working with XML Data in MySQL

November 19, 2009

In the “Working with XML Data in MySQL” article, we explored some of MySQL’s XML capabilities, including the --xml command line option, which provides a means of formatting query output as XML, as well as a couple of new XML-specific functions that were introduced in MySQL 5.1.5. In today’s article, we’ll continue to explore XML support in MySQL. Specifically, we’ll look at a nicer way to format our resultset so that the proper headers appear in the column headings, rather than the supplied expressions. We’ll also expand the last example of the previous installment to retrieve all the records from the XML document, using a stored procedure.

Adding Our Own Headers to ExtractValue’s Output

If you recall, our final example of the last article imported the contents of an XML file into a variable using the LOAD_FILE() function and then selected some fields from it using the ExtractValue() function:

CREATE TEMPORARY TABLE client_citizenship (
    id INT NOT NULL AUTO_INCREMENT PRIMARY KEY,
    xml_data TEXT NOT NULL
); 
 
SET @xml = LOAD_FILE("c:\\client_citizenships.xml"); 
 
INSERT INTO client_citizenship VALUES (NULL, @xml); 
 
SELECT xml_data FROM client_citizenship LIMIT 1 INTO @xml;
 
SELECT ExtractValue(@xml, '//row[2]/field[1]/@name'),
        ExtractValue(@xml, '//row[2]/field[1]');

The last SELECT statement extracted the first <field> of the second <row> node:

...
</row>
 
<row> (row[2])
    <field name="client_id">2</field> (field[1])
    <field name="date_of_birth">1944-01-15</field>
...

This produced the following resultset:

+----------------------------------------------+----------------------------------------+
|ExtractValue(@xml, "//row[2]/field[1]/@name") |ExtractValue(@xml, '//row[2]/field[1]') |
+----------------------------------------------+----------------------------------------+
|client_id                                     |2                                       |
+----------------------------------------------+----------------------------------------+

Notice that MySQL uses the expressions as the column headers. To format the resultset with the field name in the header, we need to use a Prepared Statement. A Prepared Statement is a special type of SQL statement that has been precompiled. This means that when the Prepared Statement is executed, the DBMS can just run the Prepared Statement SQL without having to compile it first. Therefore it normally reduces execution time to use a Prepared Statement over a regular query when you want to execute the same statement many times. In this instance, the advantage to using a Prepared Statement is that we can set the header in one statement, and then concatenate it to our select string:

SET @header = SELECT ExtractValue(@xml, "//row[2]/field[1]/@name");
SET @qry = CONCAT("SELECT ExtractValue(@xml, \"//row[2]/field[1]\") AS ", @header, ";");
PREPARE stmt FROM @qry;
EXECUTE stmt;
DEALLOCATE PREPARE stmt;

Now our data is formatted how we’re used to seeing it:

+-----------+
| client_id |
+-----------+
| 2         |
+-----------+

Using the ExtractValue() Function Within a Stored Procedure

One obvious limitation to the above approach is that the rows are hard-coded so, unless you’re after specific values, it won’t be of much use for more generic tasks, such as importing an entire XML document into a table. For that, we need to use a stored proc.

MySqlDump

The MySqlDump client is a backup program originally written by Igor Romanenko. It can be used to dump a database or a collection of databases for backup or transfer to another database (MySQL or other). The dump typically contains SQL statements to create the table, populate it, or both. However, MySqlDump can also be used to generate files in CSV, other delimited text, or XML format, which is what we’ll be doing here.

Although there is more than one way to run MySqlDump, the syntax for what we want to do is as follows:

mysqldump --xml databasename [tables]

You can also extract to a file by using the standard UNIX/DOS method of outputting to a file. Here is the command that I used to convert my client table into an XML file:

C:\>mysqldump --xml temp_table_article client > c:\\sqldump.xml

Looking a the sqldump.xml, we can see that the MySqlDump client includes a lot more information than was produced by the --xml flag alone. Whereas it simply outputed each row, field names and values, MySqlDump separates the output into “table_structure” and “table_data” sections:

<?xml version="1.0"?>
<mysqldump xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance">
<database name="temp_table_article">
         <table_structure name="client">
                 <field Field="client_id" Type="int(10) unsigned" Null="NO" Key="PRI" Extra="auto_increment" />
                 <field Field="date_of_birth" Type="date" Null="YES" Key="" Extra="" />
                 <field Field="gender" Type="char(1)" Null="YES" Key="" Extra="" />
                 <field Field="logical_delete_indicator" Type="tinyint(1)" Null="NO" Key="" Default="0" Extra="" />
                 <key Table="client" Non_unique="0" Key_name="PRIMARY" Seq_in_index="1" Column_name="client_id" 
				   Collation="A" Cardinality="4" Null="" Index_type="BTREE" Comment="" />
                 <key Table="client" Non_unique="0" Key_name="client_id" Seq_in_index="1" Column_name="client_id" 
				   Collation="A" Cardinality="4" Null="" Index_type="BTREE" Comment="" />
                 <options Name="client" Engine="InnoDB" Version="10" Row_format="Compact" Rows="4" Avg_row_length="4096" 
				   Data_length="16384" Max_data_length="0" Index_length="16384" Data_free="10485760" 
				   Auto_increment="5" 
				   Create_time="2009-10-05 17:51:34" Collation="latin1_swedish_ci" Create_options="" 
				   Comment="" />
         </table_structure>
         <table_data name="client">
         <row>
                 <field name="client_id">1</field>
                 <field name="date_of_birth">1976-02-12</field>
                 <field name="gender">M</field>
                 <field name="logical_delete_indicator">1</field>
         </row>
         <row>
                 <field name="client_id">2</field>
                 <field name="date_of_birth">1944-01-15</field>
                 <field name="gender">F</field>
                 <field name="logical_delete_indicator">0</field>
         </row>
         <row>
                 <field name="client_id">3</field>
                 <field name="date_of_birth">1956-06-04</field>
                 <field name="gender">M</field>
                 <field name="logical_delete_indicator">1</field>
         </row>
         <row>
                 <field name="client_id">4</field>
                 <field name="date_of_birth">1938-11-19</field>
                 <field name="gender">F</field>
                 <field name="logical_delete_indicator">0</field>
         </row>
         </table_data>
</database>
</mysqldump>

The reason that we used this tool to create the XML data structure is because there are already stored procedures available to insert MySqlDump XML data into a table. One such proc was written by MySQL developer Alexander Barkov. His xmldump_load procedure extracts data from the XML elements and attributes found in a file created using mysqldump --xml, and inserts this data into the columns of a MySQL table. Here is the full source of this stored procedure:

DELIMITER |
 
DROP PROCEDURE IF EXISTS xmldump_load |
 
CREATE PROCEDURE xmldump_load(     file_name VARCHAR(128),
                          database_name VARCHAR(128),
                          table_name VARCHAR(128))
BEGIN
  DECLARE xml TEXT;
 
  DECLARE nrows INT;
  DECLARE rownum INT DEFAULT 1;
 
  DECLARE ncols INT;
  DECLARE colnum INT DEFAULT 1;
 
  DECLARE ins_list TEXT DEFAULT '';
  DECLARE val_list TEXT DEFAULT '';
 
  DECLARE tmp VARCHAR(255);
 
  # load the XML file's contents into a string
  SET xml = LOAD_FILE(file_name);
 
  # get the number of <row>s in this table
  SET nrows = ExtractValue(xml, 
   'count(/mysqldump/database[@name=$database_name]/table_data[@name=$table_name]/row)');
 
  # get the number of <field>s (columns) in this table
  SET ncols = ExtractValue(xml, 
'count(/mysqldump/database[@name=$database_name]/table_data[@name=$table_name]/row[1]/field)');
 
  # for each <row>
  WHILE rownum <= nrows DO
 
    # for each <field> (column)
    WHILE colnum <= ncols DO
      SET tmp = ExtractValue(xml, 
'/mysqldump/database[@name=$database_name]/table_data[@name=$table_name]/row[$rownum]/field[$colnum]/@name');
      SET ins_list = CONCAT(ins_list, tmp, IF(colnum<ncols, ',', ''));
      SET tmp = ExtractValue(xml, 
'/mysqldump/database[@name=$database_name]/table_data[@name=$table_name]/row[$rownum]/field[$colnum]');
      SET val_list = CONCAT(val_list, '''', tmp ,'''', IF(colnum<ncols, ',', ''));
      SET colnum = colnum + 1;
    END WHILE;
 
    SET @ins_text = CONCAT('INSERT INTO t1 (', ins_list, ') VALUES (', val_list, ')');
 
    SET ins_list = '';
    SET val_list = '';
 
    PREPARE stmt FROM @ins_text;
    EXECUTE stmt;
 
    SET rownum = rownum + 1;
    SET colnum = 1;
  END WHILE;
END |
 
DELIMITER ;

Examine the code carefully and you’ll see that the xmldump_load proc uses the same XML tools as we’ve used so far, such as the LOAD_FILE() and ExtractValue() functions. What it adds are two nested While loops to iterate through every row and column.

Here are the steps that one would take to import the sqldump.xml file that we created earlier:

The first thing we need to do is import the xmldump_load proc into MySQL if this is the first time that we use it:

C:\> mysql db_name < text_file
 
C:\> mysql temp_table_article < c:\xmldump_load.sql

You can also load the proc from within the MySql client using the source or \. commands:

mysql> source c:xmldump_load.sql;
 
OR
 
mysql> \. c:xmldump_load.sql;

The proc accepts the three following input parameters:

the XML input file name

the database name to write to

the table name to create

Now we can supply the sqldump.xml file that we created earlier, along with a database and table name:

mysql> call xmldump_load('c:\sqldump.xml ', 'client_info', 'client');

That will insert the contents of the sqldump.xml file into the client table of the client_info database.

Coming Soon...

A new SQL statement has been accepted for MySQL 6.0, and will be available in version 6.0.3. LOAD XML greatly simplifies the task of importing data from an XML file into a MySQL table, without having to use the stored procedure discussed previously. At the time of this writing, version 5.4 is in Beta, so you’ll have to wait a bit for that.

» See All Articles by Columnist Rob Gravelle