MySQL Cookbook

Producing XML Output

Problem

You’d like to turn a query result into XML.

Solution

mysql can do that for you.

Discussion

mysql creates an XML document from the result of a query if you use the -X (or --xml) option. Here’s an example that shows the difference between tabular format and the XML created from the same query:

% mysql -e "SELECT * FROM limbs WHERE legs=0" cookbook
+------------+------+------+
| thing      | legs | arms |
+------------+------+------+
| squid      |    0 |   10 |
| octopus    |    0 |    8 |
| fish       |    0 |    0 |
| phonograph |    0 |    1 |
+------------+------+------+
% mysql -X -e "SELECT * FROM limbs WHERE legs=0" cookbook
<?xml version="1.0"?>

<resultset statement="SELECT * FROM limbs WHERE legs=0">
  <row>
    <thing>squid</thing>
    <legs>0</legs>
    <arms>10</arms>
  </row>

  <row>
    <thing>octopus</thing>
    <legs>0</legs>
    <arms>8</arms>
  </row>

  <row>
    <thing>fish</thing>
    <legs>0</legs>
    <arms>0</arms>
  </row>

  <row>
    <thing>phonograph</thing>
    <legs>0</legs>
    <arms>1</arms>
  </row>
</resultset>

-X and --xml may be used as of MySQL 4.0. If your version of MySQL is older than that, you can write your own XML generator. See Recipe 10.42.