Skip to Main Content
MySQL Cookbook
book

MySQL Cookbook

by Paul DuBois
October 2002
Intermediate to advanced content levelIntermediate to advanced
1024 pages
27h 26m
English
O'Reilly Media, Inc.
Content preview from MySQL Cookbook

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::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 (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 | +---------+------+-------+ ...
Become an O’Reilly member and get unlimited access to this title plus top books and audiobooks from O’Reilly and nearly 200 top publishers, thousands of courses curated by job role, 150+ live events each month,
and much more.
Start your free trial

You might also like

MySQL Reference Manual

MySQL Reference Manual

Michael Widenius, David Axmark, Kaj Arno
High Performance MySQL

High Performance MySQL

Jeremy D. Zawodny, Derek J. Balling
MySQL Stored Procedure Programming

MySQL Stored Procedure Programming

Guy Harrison, Steven Feuerstein

Publisher Resources

ISBN: 0596001452Catalog PageErrata