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) mysql> use horses; Database changed mysql> show tables; +------------------+ | Tables_in_horses | +------------------+ | horse | +------------------+ 1 row in set (0.00 sec) mysql> describe horse; +-------+-------------+------+-----+---------+-------+ | Field | Type | Null | Key | Default | Extra | +-------+-------------+------+-----+---------+-------+ | id | tinyint(4) | | PRI | 0 | | | name | varchar(20) | | | | | | owner | varchar(20) | | | | | | age | tinyint(2) | | | 0 | | | breed | varchar(30) | | | | | +-------+-------------+------+-----+---------+-------+ 5 rows in set (0.00 sec) mysql> select * from horse; +----+---------+--------+-----+---------------+ | id | name | owner | age | breed | +----+---------+--------+-----+---------------+ | 1 | Babes | Tom | 12 | Quarter | | 2 | Stanley | Mike | 13 | Quarter | | 3 | Sassy | Aubrey | 16 | Welsh-Hackney | | 4 | Sissy | Cristi | 10 | Quarter-Arab | | 5 | Gypsy | Margie | 7 | Albino | | 6 | Jubal | Kathy | 4 | Pinto | +----+---------+--------+-----+---------------+ 6 rows in set (0.00 sec) mysql> quit Bye You will not have this database in the downloaded files; however, you will have the files produced in the following exercise (i.e., horses.xml and mysql.xsl). It's now time to dump the data out of the database as XML. We'll do that with the mysqldump tool. At a command prompt, type this line: mysqldump --xml horses > horses.xml The tool extracts the information in the database horses and redirects it into the file horses.xml, which looks like this (Example 3-30). Example 3-30. horses.xml <?xml version="1.0"?> <mysqldump> <database name="horses"> <table name="horse"> <row> <field name="id">1</field> <field name="name">Babes</field> <field name="owner">Tom</field> <field name="age">12</field> <field name="breed">Quarter</field> </row> <row> <field name="id">2</field> <field name="name">Stanley</field> <field name="owner">Mike</field> <field name="age">13</field> <field name="breed">Quarter</field> </row> <row> <field name="id">3</field> <field name="name">Sassy</field> <field name="owner">Aubrey</field> <field name="age">16</field> <field name="breed">Welsh-Hackney</field> </row> <row> <field name="id">4</field> <field name="name">Sissy</field> <field name="owner">Cristi</field> <field name="age">10</field> <field name="breed">Quarter-Arab</field> </row> <row> <field name="id">5</field> <field name="name">Gypsy</field> <field name="owner">Margie</field> <field name="age">7</field> <field name="breed">Albino</field> </row> <row> <field name="id">6</field> <field name="name">Jubal</field> <field name="owner">Kathy</field> <field name="age">4</field> <field name="breed">Pinto</field> </row> </table> </database> </mysqldump> The elements in this XML document mysqldump, database, table, row, and field are consistent. That's why you will be able to use the following stylesheet, mysqldump.xsl (Example 3-31), with most any mysqldump XML. Example 3-31. mysqldump.xsl <xsl:stylesheet version="1.0" xmlns:xsl="http://www.w3.org/1999/XSL/Transform"> <xsl:output method="xml" encoding="UTF-8"/> <xsl:template match="mysqldump"> <xsl:apply-templates select="database"/> </xsl:template> <xsl:template match="database"> <xsl:element name="{@name}"> <xsl:apply-templates select="table"/> </xsl:element> </xsl:template> <xsl:template match="table"> <xsl:apply-templates select="row"/> </xsl:template> <xsl:template match="row"> <xsl:element name="{../@name}"> <xsl:apply-templates select="field"/> </xsl:element> </xsl:template> <xsl:template match="field"> <xsl:element name="{@name}"> <xsl:value-of select="."/> </xsl:element> </xsl:template> </xsl:stylesheet> The stylesheet finds each of the common element names in the mysqldump output, and creates new elements based on the values of the name attributes. Process it with this command using Xalan C++: xalan -i 2 horses.xml mysqldump.xsl The -i option followed by 2 means that the output will be indented by two spaces at each level in the structure. Xalan will produce the output in Example 3-32. Example 3-32. Output from mysqldump.xsl <?xml version="1.0" encoding="UTF-8"?> <horses> <horse> <id>1</id> <name>Babes</name> <owner>Tom</owner> <age>12</age> <breed>Quarter</breed> </horse> <horse> <id>2</id> <name>Stanley</name> <owner>Mike</owner> <age>13</age> <breed>Quarter</breed> </horse> <horse> <id>3</id> <name>Sassy</name> <owner>Aubrey</owner> <age>16</age> <breed>Welsh-Hackney</breed> </horse> <horse> <id>4</id> <name>Sissy</name> <owner>Cristi</owner> <age>10</age> <breed>Quarter-Arab</breed> </horse> <horse> <id>5</id> <name>Gypsy</name> <owner>Margie</owner> <age>7</age> <breed>Albino</breed> </horse> <horse> <id>6</id> <name>Jubal</name> <owner>Kathy</owner> <age>4</age> <breed>Pinto</breed> </horse> </horses> |