MySQL Cookbook, 2nd Edition

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

You can use mysql to produce XML-format output from a query result (Producing HTML or XML Output).

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 (Writing Your Own Export Programs), but doesn’t take options for such things as the quote or delimiter characters. They are unneeded for reading XML, because that is done by standard XML parsing routines. The options that mysql_to_xml.pl does understand are:

--execute=query, -equery

Execute query, and export its output.

--table=tbl_name, -ttbl_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 such as --user or --host. The final argument on the command line should be the database name, unless it’s implicit in the query.

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