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:
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
,
-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 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:
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, 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:
<?xml version="1.0" encoding="UTF-8"?> <rowset> <select query="SELECT * FROM expt"> <row> <subject>Jane</subject> <test>A</test> <score>47</score> </row> <row> <subject>Jane</subject> <test>B</test> <score>50</score> </row> <row> <subject>Jane</subject> <test>C</test> </row> <row> <subject>Jane</subject> <test>D</test> </row> <row> <subject>Marvin</subject> <test>A</test> <score>52</score> </row> <row> <subject>Marvin</subject> <test>B</test> <score>45</score> </row> <row> <subject>Marvin</subject> <test>C</test> <score>53</score> </row> <row> <subject>Marvin</subject> <test>D</test> </row> </select> </rowset>
Each table row is written as a <row>
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 produces this output with very little code after it
processes the command-line arguments and connects to the MySQL server.
The XML-related parts of mysql_to_xml.pl are the use
statements that pull in the necessary
modules and the code to set up and use 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, and then connects that object to DBI and
issues the query:
#!/usr/bin/perl # mysql_to_xml.pl - given a database and table name, # dump the table to the standard output in XML format. use strict; use warnings; 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 ($stmt); $dbh->disconnect ();
Other languages might have library modules to perform similar
XML export operations. For example, the Ruby DBI::Utils::XMLFormatter
module has a table
method that
easily exports a query result as XML. Here’s a simple script that uses
it:
#!/usr/bin/ruby -w # xmlformatter.rb - demonstrate DBI::Utils::XMLFormatter.table method require "Cookbook" stmt = "SELECT * FROM expt" # override statement with command line argument if one was given stmt = ARGV[0] if ARGV.length > 0 begin dbh = Cookbook.connect rescue DBI::DatabaseError => e puts "Could not connect to server" puts "Error code: #{e.err}" puts "Error message: #{e.errstr}" end DBI::Utils::XMLFormatter.table(dbh.select_all(stmt)) dbh.disconnect
13.59.116.74