Home

Language Translator

Login Form

Using XML in MySQL 5.1 and 6.0

Article Index
Using XML in MySQL 5.1 and 6.0
Page 2
Page 3
Page 4
Page 5
Page 6

Page 1 of 6

Using XML in MySQL 5.1 and 6.0

In this article, we discuss the XML functionality available in MySQL, with an emphasis on new features coming online in MySQL 5.1 and MySQL 6.0. We assume that you already have a working knowledge of XML, and that you know what the terms “valid” and “well-formed” mean. We also assume that you have some knowledge of XPath.

We cover the following topics:

Methods for outputting MySQL data in XML format, including the use of lib_mysqludf_xql, a third-party library that can be used for this task
Using the functions (new in MySQL 5.1) ExtractValue() and UpdateXML() for working with XML and XPath
Storing data from XML in a MySQL database using the LOAD XML statement (implemented in MySQL 6.0)
Some security considerations to keep in mind when using these techniques

Getting XML into and out of MySQL

In this section, we discuss how to retrieve data from MySQL in XML format, and how to store data obtained from an XML source in a MySQL database.

Exporting Data

In this section, we start with some data already stored in a MySQL table, and demonstrate several different ways to output it in XML format.

Using the --xml option. Both the mysql and mysqldump client programs support a startup option that causes them to produce XML output. Here is a brief example using the mysql client:

shell> mysql -uroot -e "SHOW VARIABLES LIKE '%version%'" --xml
<?xml version="1.0"?>

<resultset statement="SHOW VARIABLES LIKE '%version%'" 
    xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance">
  <row>
        <field name="Variable_name">protocol_version</field>
        <field name="Value">10</field>
  </row>

  <row>
        <field name="Variable_name">version</field>
        <field name="Value">5.1.22-beta-debug</field>
  </row>

  <row>
        <field name="Variable_name">version_comment</field>
        <field name="Value">Source distribution</field>
  </row>

  <row>
        <field name="Variable_name">version_compile_machine</field>
        <field name="Value">x86_64</field>
  </row>

  <row>
        <field name="Variable_name">version_compile_os</field>
        <field name="Value">suse-linux-gnu</field>
  </row>
</resultset>

The content of the <field name="Value"> elements corresponds to the values found in the Value column displayed when the same statement is executed in the mysql client without the --xml option, as shown here:

shell> mysql -uroot -e "SHOW VARIABLES LIKE '%version%'"
+-------------------------+---------------------+
| Variable_name           | Value               |
+-------------------------+---------------------+
| protocol_version        | 10                  |
| version                 | 5.1.22-beta-debug   |
| version_comment         | Source distribution |
| version_compile_machine | x86_64              |
| version_compile_os      | suse-linux-gnu      |
+-------------------------+---------------------+

Naturally, the values themselves depend on the MySQL version that you are using and the machine on which it is running, so if you run the same statement, your results are likely to be different from what is shown here.

Example table for XML export. The remainder of this section uses a table created and populated by the following SQL statements:

CREATE SCHEMA xmltest;

CREATE TABLE xmltest.cities (    
    name CHAR(35) NOT NULL DEFAULT '',
    country CHAR(52) NOT NULL DEFAULT '',
    population int(11) NOT NULL DEFAULT '0'
);

INSERT INTO cities VALUES ('Mumbai (Bombay)','India',10500000);
INSERT INTO cities VALUES ('Seoul','South Korea',9981619);
INSERT INTO cities VALUES ('São Paulo','Brazil',9968485); 
INSERT INTO cities VALUES ('Shanghai','China',9696300);
INSERT INTO cities VALUES ('Jakarta','Indonesia',9604900);
INSERT INTO cities VALUES ('Karachi','Pakistan',9269265);
INSERT INTO cities VALUES ('Istanbul','Turkey',8787958);
INSERT INTO cities VALUES ('Ciudad de México','Mexico',8591309); 
INSERT INTO cities VALUES ('Moscow','Russian Federation',8389200);
INSERT INTO cities VALUES ('New York','United States',8008278);

Note

This table was originally created using the following SQL statement on the venerable world example database:

CREATE TABLE xmltest.cities 
    SELECT  i.Name AS name, 
o.Name AS country, 
i.Population AS population 
    FROM City i JOIN Country o ON i.CountryCode=o.Code 
    ORDER BY i.Population DESC LIMIT 10;

You can obtain a copy of the world database from http://dev.mysql.com/doc/.

Beginning with MySQL 5.1.12, the <field> and <row> format produced by the mysql client matches that produced by mysqldump. However, the root element in the output of mysql --xml, is <resultset>, whose statement attribute contains the SQL statement passed to mysql, as shown here:

shell> mysql -uroot --xml -e 'SELECT * FROM xmltest.cities ORDER BY name'
<?xml version="1.0"?>

<resultset statement="SELECT * FROM xmltest.cities ORDER BY name" 
    xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance">
  <row>
        <field name="name">Ciudad de Méico</field>
        <field name="country">Mexico</field>
        <field name="population">8591309</field>
  </row>

  <row>
        <field name="name">Istanbul</field>
        <field name="country">Turkey</field>
        <field name="population">8787958</field>
  </row>

  <row>
        <field name="name">Jakarta</field>
        <field name="country">Indonesia</field>
        <field name="population">9604900</field>
  </row>

  <row>
        <field name="name">Karachi</field>
        <field name="country">Pakistan</field>
        <field name="population">9269265</field>
  </row>

  <row>
        <field name="name">Moscow</field>
        <field name="country">Russian Federation</field>
        <field name="population">8389200</field>
  </row>

  <row>
        <field name="name">Mumbai (Bombay)</field>
        <field name="country">India</field>
        <field name="population">10500000</field>
  </row>

  <row>
        <field name="name">New York</field>
        <field name="country">United States</field>
        <field name="population">8008278</field>
  </row>

  <row>
        <field name="name">São Paulo</field>
        <field name="country">Brazil</field>
        <field name="population">9968485</field>
  </row>

  <row>
        <field name="name">Seoul</field>
        <field name="country">South Korea</field>
        <field name="population">9981619</field>
  </row>

  <row>
        <field name="name">Shanghai</field>
        <field name="country">China</field>
        <field name="population">9696300</field>
  </row>
</resultset>

The output of mysqldump--xml is structured somewhat differently, as shown here:

shell> mysqldump --xml xmltest cities
<?xml version="1.0"?>
<mysqldump xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance">
<database name="xmltest">
  <table_structure name="cities">
    <field Field="name" Type="char(35)" Null="NO" Key="" Default="" 
  Extra=""/>
    <field Field="country" Type="char(52)" Null="NO" Key="" Default="" 
  Extra=""/>
    <field Field="population" Type="int(11)" Null="NO" Key="" Default="0" 
  Extra=""/>
    <options Name="cities" Engine="MyISAM" Version="10" Row_format="Fixed" 
    Rows="10" Avg_row_length="92" Data_length="920" 
    Max_data_length="25895697857380351" Index_length="1024" 
    Data_free="0" Create_time="2007-08-24 14:19:42" 
    Update_time="2007-08-24 14:19:42" Collation="latin1_swedish_ci" 
    Create_options="" Comment="" />
        </table_structure>
        <table_data name="cities">
        <row>
    <field name="name">Mumbai (Bombay)</field>
    <field name="country">India</field>
    <field name="population">10500000</field>
        </row>
        <row>
    <field name="name">Seoul</field>
    <field name="country">South Korea</field>
    <field name="population">9981619</field>
        </row>
        <row>
    <field name="name">São Paulo</field>
    <field name="country">Brazil</field>
    <field name="population">9968485</field>
        </row>
        <row>
    <field name="name">Shanghai</field>
    <field name="country">China</field>
    <field name="population">9696300</field>
        </row>
        <row>
    <field name="name">Jakarta</field>
    <field name="country">Indonesia</field>
    <field name="population">9604900</field>
        </row>
        <row>
    <field name="name">Karachi</field>
    <field name="country">Pakistan</field>
    <field name="population">9269265</field>
        </row>
        <row>
    <field name="name">Istanbul</field>
    <field name="country">Turkey</field>
    <field name="population">8787958</field>
        </row>
        <row>
    <field name="name">Ciudad de México</field>
    <field name="country">Mexico</field>
    <field name="population">8591309</field>
        </row>
        <row>
    <field name="name">Moscow</field>
    <field name="country">Russian Federation</field>
    <field name="population">8389200</field>
        </row>
        <row>
    <field name="name">New York</field>
    <field name="country">United States</field>
    <field name="population">8008278</field>
        </row>
        </table_data>
</database>
</mysqldump>

Note

The formatting of some of the XML output has been altered slightly to fit the space available on a printed page.

mysqldump--xml employs the following elements:

The root element of the XML document representing the dump is <mysqldump>
The table definitions and data belonging to each database represented in the dump are wrapped together in a <database> element whose name attribute value is the name of that database
Each table definition is wrapped in a <table_structure> element
The data from each table is wrapped in a <table_data> element, and is comprised of <field> and <row> elements

To save the output of either mysql or mysqldump to a file, simply use the > operator with the desired filename, as shown here:

shell> mysql -uroot --xml xmltest -e 'SELECT name FROM cities LIMIT 2' > /tmp/2cities.xml
shell> more /tmp/2cities.xml
<?xml version="1.0"?>

<resultset statement="SELECT name,country FROM cities LIMIT 2" 
    xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance">
  <row>
        <field name="name">Mumbai (Bombay)</field>
        <field name="country">India</field>
  </row>

  <row>
        <field name="name">Seoul</field>
        <field name="country">South Korea</field>
  </row>
</resultset>

Note

An XML namespace declaration is included in the XML output of both mysql and mysqldump, beginning with MySQL 5.1.18.

There may be times when your application expects an XML format that is different from what is normally obtain from mysql or mysqldump. Suppose, for example, that your application expects a format such as this one:

<cities>
  <city>name1<city>
  <city>name2<city>
  <-- etc. -->
</cities>

One way to obtain such output would be to use concatenation with the CONCAT() and GROUP_CONCAT() functions, like this:

mysql> SELECT CONCAT('\n<cities>\n', 
    ->   GROUP_CONCAT('  <city>', name, '</city>\n' SEPARATOR ''), 
    ->   '</cities>') AS xmldoc 
    -> FROM cities\G

*************************** 1. row ***************************
xmldoc:
<cities>
  <city>Mumbai (Bombay)</city>
  <city>Seoul</city>
  <city>Sã Paulo</city>
  <city>Shanghai</city>
  <city>Jakarta</city>
  <city>Karachi</city>
  <city>Istanbul</city>
  <city>Ciudad de Méico</city>
  <city>Moscow</city>
  <city>New York</city>
</cities>
1 row in set (0.01 sec)

Another such example is this format:

<cities>
  <city name="name1" population="population1"/>
  <city name="name2" population="population2"/>
  <-- etc. -->
</cities>

This can be produced as shown here:

mysql> SELECT CONCAT(
    ->           '\n<cities>',
    ->           GROUP_CONCAT(
    -> '\n\t<city name="', name, '" population="', population, '"/>'
    -> SEPARATOR ''
    ->           ),
    ->           '\n</cities>'
    ->         ) AS xmldoc
    ->   FROM cities\G
*************************** 1. row ***************************
xmldoc:
<cities>
        <city name="Mumbai (Bombay)" population="10500000"/>
        <city name="Seoul" population="9981619"/>
        <city name="Sã Paulo" population="9968485"/>
        <city name="Shanghai" population="9696300"/>
        <city name="Jakarta" population="9604900"/>
        <city name="Karachi" population="9269265"/>
        <city name="Istanbul" population="8787958"/>
        <city name="Ciudad de Méico" population="8591309"/>
        <city name="Moscow" population="8389200"/>
        <city name="New York" population="8008278"/>
</cities>
1 row in set (0.01 sec)

You can see that generating even relatively simple XML output by such means can rapidly grow very complicated. While you can wrap such SELECT statements in stored procedures, the fact that MySQL stored routines cannot (at least at present) take a variable number of arguments. Fortunately, there is a third-party library that can be used with MySQL to make this task easier.

Using the lib_mysqludf_xqllibrary. The lib_mysqludf_xql library is a set of UDFs originally written and placed under the GPL by Arnold Daniels. The source for these is now housed at www.mysqludf.org.

mysql> SELECT xql_element('city', name) FROM cities;
+-------------------------------+
| xql_element('city', name)     |
+-------------------------------+
| <city>Mumbai (Bombay)</city>  |
| <city>Seoul</city>|
| <city>Sã Paulo</city>         |
| <city>Shanghai</city>         |
| <city>Jakarta</city>          |
| <city>Karachi</city>          |
| <city>Istanbul</city>         |
| <city>Ciudad de Méico</city>  |
| <city>Moscow</city>           |
| <city>New York</city>         |
+--------------------------------+
10 rows in set (0.00 sec)

mysql> SELECT xql_element('city', NULL, name AS 'name') FROM cities;
+-------------------------------------------+
| xql_element('city', NULL, name AS 'name') |
+-------------------------------------------+
| <city name="Mumbai (Bombay)"/>|
| <city name="Seoul"/>          |
| <city name="S&#x3BE0;Paulo"/> |
| <city name="Shanghai"/>       |
| <city name="Jakarta"/>        |
| <city name="Karachi"/>        |
| <city name="Istanbul"/>       |
| <city name="Ciudad de M&#x9E29;co"/>      |
| <city name="Moscow"/>         |
| <city name="New York"/>       |
+-------------------------------------------+
10 rows in set (0.00 sec)

mysql> SELECT xql_element('city', NULL, name AS 'name', population AS 'population')
    ->     FROM cities;
+-----------------------------------------------------------------------+
| xql_element('city', NULL, name AS 'name', population AS 'population') |
+-----------------------------------------------------------------------+
| <city name="Mumbai (Bombay)" population="10500000"/>      |
| <city name="Seoul" population="9981619"/>     |
| <city name="S&#x3BE0;Paulo" population="9968485"/>        |
| <city name="Shanghai" population="9696300"/>  |
| <city name="Jakarta" population="9604900"/>   |
| <city name="Karachi" population="9269265"/>   |
| <city name="Istanbul" population="8787958"/>  |
| <city name="Ciudad de M&#x9E29;co" population="8591309"/> |
| <city name="Moscow" population="8389200"/>    |
| <city name="New York" population="8008278"/>  |
+-----------------------------------------------------------------------+
10 rows in set (0.00 sec)