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::DBIissues a query over a DBI connection and passes the result to a suitable output writer.XML::Handler::YAWriterprovides 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,-equery Execute
queryand export its output.-
--table=tbl_name,-ttbl_name Export the contents of the named table. This is equivalent to using
--executeto specify aqueryvalue ofSELECT*FROMtbl_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 | +---------+------+-------+ ...