XML - Managing Data Exchange/Handling XML with MySQL

---

XML - Managing Data Exchange Chapters Appendices Exercises

Related Topics Computer Science Home Library and Information Science Home Markup Languages

Get Involved To do list Contributors list Contributing to Wikibooks

Previous Chapter	Next Chapter
← Business Intelligence and XML	XML Encryption →

---

Author: Shayla S. Lee 01:39, 15 November 2005 (UTC)

Introduction

MySQL is an open source relational database that supports XML. You can use the MySQL command line or a programming language of your choice to convert your MySQL databases and or tables to a well formed XML document.

Supported Versions

XML is supported in MySQL version 3.23.48 and higher. A free version of MySQL can be downloaded from MySQL.com.

Using the MySQL Command Line

Use the --xml or -X option with either the mysqldump or mysql command to produce XML output.

mysqldump Syntax:

mysqldump --xml -u username -p databasename [tablename] > filename.xml

mysql Syntax:

\T "filename.xml"
mysql -X -u username -p databasename [tablename]

OR

\T "filename.xml"
mysql -X -u username -p databasename tablename -e
'select columnname, columnname
 from tablename'

In the latter mysql syntax example, you can also specify a where condition as well as restrict the where condition just as you would in a regular sql select statement.

Explanation of commands and options:

mysqldump is a mysql output command.

\T is a mysql output command.

-e is a mysql option that tells mysql to execute the following select statement.

--xml is the mysql option for producing XML output.

-u is a mysql option which tells mysql that the next command line item is your username.

username is your mysql username. It will be used to authenticate you to the mysql database.

-p is a mysqldump option that tells mysql that the next command line item is your password. If do not want your password to be visible on the command line, then do not supply your password after the -p option and mysql will prompt you for it later.

databasename is the name of the database that you want to output to xml.

tablename is the name of the table that you want to output to xml. Supplying the tablename is optional.

The > symbol is the output symbol that tells mysql to output the results to the following filename.

filename.xml is the filename that you want to output the XML results.