Generate XML from MySQL

Using MySQL and want to use the data stored there elsewhere? Dump XML out of a MySQL database and then transform it with XSLT.

MySQL ( is a popular, nearly ubiquitous, multiplatform database engine, available under both open source and commercial licenses. It is commonly used as a data store for web sites. This hack shows you how to examine a MySQL database, dump its contents as XML, and then use XSLT to transform and refine the result.

You can download MySQL from It has versions for Windows, Linux, FreeBSD, NetBSD, Solaris, SCO, and even OS/2. For information on installing MySQL, see If you are unfamiliar with MySQL, you can get up to speed quickly by reading the tutorial at

Here we have a database horses containing the table horse. The table has five fields: id, name, owner, age, and breed. First we invoke the command-line tool mysql, and then issue some Structured Query Language (SQL) commands, shown in Example 3-29.

Example 3-29. SQL commands

C:\Hacks\examples>mysql Welcome to the MySQL monitor. Commands end with ; or \g. Your MySQL connection id is 12 to server version: 3.23.54-max-nt Type 'help;' or '\h' for help. Type '\c' to clear the buffer. mysql> show databases; +----------+ | Database | +----------+ | horses | +----------+ 1 row in set (0.00 sec) ...

Get XML Hacks now with O’Reilly online learning.

O’Reilly members experience live online training, plus books, videos, and digital content from 200+ publishers.