1.25 Producing XML Output

1.25.1 Problem

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

1.25.2 Solution

mysql can do that for you.

1.25.3 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


        squid     0     10            octopus     0     8            fish     0     0            phonograph     0     1    

-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