Output MySQL query results to XML from the command prompt

In a previous tutorial I wrote about the ability to export MySQL query results out to a CSV file via the command prompt. There is another method for getting your query results into a file without using a server-side language, and it involves command line startup options. In this example I will push the results to XML, although an HTML option is available as well.

MySQL 5 supports options at the command prompt, just like UNIX. Unlike UNIX, the MySQL options are only utilized when first logging on to the MySQL shell. This is different than MySQL commands, which are preceded by a backslash (\), and are executed after successfully logging on to the MySQL shell. If you have ever used the MySQL command line, then you have probably used options, such as (-h)(host), (-u)(username), and (-p )(password).

The following code takes the previously named options, and an additional (–xml)(XML) option. This informs MySQL that each set of query results should be output as valid XML, and not to use the standard pipe delimiters.

MySQL:

shell> mysql -h yourdomain.com -u root -p --xml

Provide your password, and then choose your database. Before you execute a query, execute the following command.

MySQL:

mysql> tee /path/to/directory/file.xml

All query results will now be logged into the file you specified, and formatted as valid XML. Remember, errors will be logged as well, so be sure you have your query correct if you want a clean XML file. To discontinue logging to an outfile, simply execute the (\t)(notee) option. For more information on MySQL options and commands, the official documentation can be found here.

• Posted by Brian
• September 27th, 2007