Using XML in MySQL 5.1 and 6.0

Table of Contents

• Getting XML into and out of MySQL
  • Exporting Data
  • Importing XML and XML Data
• XPath Functionality
  • The ExtractValue()
  • The UpdateXML() Function
  • XPath support in MySQL 5.1 and 6.0
• Security Considerations
  • Loading Data from Files
  • XML and MySQL Privileges
  • XPath Injection

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      |
+-------------------------+---------------------+

Full article...