Database Reference
In-Depth Information
• XML::Generator::DBI executes 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
(see
Recipe 11.5
), but doesn't take options for such things as the quote or delimiter characters.
They are unneeded for writing XML because the XML writer module handles those
issues.
mysql_to_xml.pl
understands these options:
--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 a table named
expt
contains test scores from an experiment:
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 export the contents of
expt
, 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>
