You want to export the result of a query as an XML document.
mysql can do that, or you can write your own exporter.
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:
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 | +---------+------+-------+ | Jane | A | 47 | | Jane | B | 50 | | Jane | C | NULL | | Jane | D | NULL | | Marvin | A | 52 | | Marvin | B | 45 | | Marvin | C | 53 | | Marvin | D | NULL | +---------+------+-------+
To do that, you can invoke mysql_to_xml.pl using either of the following commands:
% mysql_to_xml.pl --execute="SELECT * FROM expt" cookbook > expt.xml % mysql_to_xml.pl --table=cookbook.expt > expt.xml
The resulting XML document, expt.xml, looks like this:
Jane A 47 Jane B 50 Jane C Jane D Marvin A 52 Marvin B 45 Marvin C 53 Marvin D
Each row is written as a element. Within a row, column names and values are used as element names and values, one element per column. Note that NULL values are omitted from the output.
The script does this with very little code after it processes the command-line arguments and connects to the MySQL server (not shown). The XML-related parts of mysql_to_xml.pl are the use statements that pull in the necessary modules and that code that sets up and uses the XML objects. Given a database handle $dbh and a query string $query, there's not a lot to this process. The code instructs the writer object to send its results to the standard output, then connects that object to DBI and issues the query:
#! /usr/bin/perl -w # mysql_to_xml.pl - given a database and table name, # dump the table to the standard output in XML format. use strict; use DBI; use XML::Generator::DBI; use XML::Handler::YAWriter; # ... process command-line options (not shown) ... # ... connect to database (not shown) ... # create output writer; "-" means "standard output" my $out = XML::Handler::YAWriter->new (AsFile => "-"); # set up connection between DBI and output writer my $gen = XML::Generator::DBI->new ( dbh => $dbh, # database handle Handler => $out, # output writer RootElement => "rowset" # document root element ); # issue query and write XML $gen->execute ($query); $dbh->disconnect ( ); exit (0);
Using the mysql Client Program
Writing MySQL-Based Programs
Record Selection Techniques
Working with Strings
Working with Dates and Times
Sorting Query Results
Modifying Tables with ALTER TABLE
Obtaining and Using Metadata
Importing and Exporting Data
Generating and Using Sequences
Using Multiple Tables
Introduction to MySQL on the Web
Incorporating Query Resultsinto Web Pages
Processing Web Input with MySQL
Using MySQL-Based Web Session Management
Appendix A. Obtaining MySQL Software
Appendix B. JSP and Tomcat Primer
Appendix C. References