MySQL, Software Development

MySQL Output Format – XML and HTML Data

Photo by Element5 Digital from Pexels

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.

where drupal_script.sql is:

mysql_xml_output

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.

mysql_html_output

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.

 

You Might Also Like