The default format of SQL Select query results is readable enough for most people. However, there are times we want to change the output format to a different format. For example, we want to output MySQL Select query results to XML or HTML format. We could do that manually, by using third-part commercial tools, or by using some flags for the mysql command, which is a MySQL client console application.
MySQL Command-Line Client
We are going to use the MySQL command-line client to format query results to XML and HTML. Typically, it comes with the MySQL server. So when you install the server, you also install MySQL command-line client. Consider the following MySQL installation on Ubuntu.
We have both the MySQL server and client after the installation.
MySQL Output as XML Format
Using the mysql client, use the --xml flag along with the other flags for connecting to a remote MySQL server.
1 | mysql --user=root --xml drupaltest < drupal_script.sql |
where drupal_script.sql is:
1 | select * from users limit 5; |
MySQL Output as HTML
To display the Select query results in HTML format, disconnect and reconnect to the MySQL server. But this time use the --html flag.
1 | mysql --user=root --html drupaltest < drupal_script.sql |
MySQL Select Query Results in XML and HTML on Video
Here is a video demonstrating this on Ubuntu 18.04 running in a virtual machine. In this video, I didn’t use the drupal_script.sql. Instead, I created a Person table and inserted two records. So, we have new data to work with.
Initially, I used the default format – table. Then, I disconnected and reconnected using the --xml flag. Then, I used the --html flag.
Would you use it? It depends! There are other tools out there that provide this feature, most are commercial ones. But the mysql command-line client could be a better choice. It is light-weight and has a small memory footprint. We could use it in conjunction with backend jobs that process huge volume of data.
