XML Hacks

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 (http://www.mysql.com) 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 http://www.mysql.com/downloads/. It has versions for Windows, Linux, FreeBSD, NetBSD, Solaris, SCO, and even OS/2. For information on installing MySQL, see http://www.mysql.com/documentation/mysql/bychapter/manual_Installing.html. If you are unfamiliar with MySQL, you can get up to speed quickly by reading the tutorial at http://www.mysql.com/documentation/mysql/bychapter/manual_Tutorial.html.

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) ...