MySQL Cookbook

Exporting Query Results as XML

Problem

You want to export the result of a query as an XML document.

Solution

mysql can do that, or you can write your own exporter.

Discussion

To produce XML-format output from a query result, you can use mysql if you have MySQL 4.0 or later. See Recipe 1.25.

You can also write your own XML-export program. One way to do this is to issue the query and then write it out, adding all the XML markup yourself. But it’s easier to install a few Perl modules and let them do the work:

• XML::Generator::DBI issues a query over a DBI connection and passes the result to a suitable output writer.

• XML::Handler::YAWriter provides one such writer.

The following script, mysql_to_xml.pl, is somewhat similar to mysql_to_text.pl (Recipe 10.18), but doesn’t take options for such things as the quote or delimiter characters. The options that it does understand are:

--execute=query, -e query

Execute query and export its output.

--table=tbl_name, -t tbl_name

Export the contents of the named table. This is equivalent to using --execute to specify a query value of SELECT * FROM tbl_name.

If necessary, you can also specify standard connection parameter options like --user or --host. The final argument on the command line should be the database name, unless it’s implicit in the query.

Suppose you want to export the contents of an experimental-data table expt that looks like this:

mysql> SELECT * FROM expt; +---------+------+-------+ | subject | test | score | +---------+------+-------+ ...