XML output from MySql

Many times you need to produce XML output from your MySql database. MySql has no built-in support for creating XML in it's SQL implementation but you can easily add support using the stored function feature.

To make xml output easier I have made three small stored SQL functions. They produce xml fragments, which you can easily put together to get a complete xml:

xml_escape(value) replace characters not allowed in xml with the escape sequences
xml_attr(name, value) create an xml attribute
xml_tag(tagname, tagvalue, attrs, subtags) create a tag, optionally with

Lets see some examples. First a real simple one:

select xml_tag('table',table_name,null,null)
from information_schema.tables
where table_schema = 'INFORMATION_SCHEMA'

Gives you something like:

<table>CHARACTER_SETS</table>
<table>COLLATIONS</table>
<table>COLLATION_CHARACTER_SET_APPLICABILITY</table>
etc...

To make this a valid xml, you need to add an xml header and a root tag, surrounding all the content. Something like this:

<?xml version="1.0" encoding="UTF-8"?>
<list>
your content...
</list>

That will give you a real simple xml, with a list of the tables
in the information schema. But perhaps your specification says
you should present the data as attributes and not as text nodes.
Let's try that:

select xml_tag('table',null,xml_attr('name',table_name),null)
from information_schema.tables
where table_schema = 'INFORMATION_SCHEMA'
Gives you:
<table name="CHARACTER_SETS" />
<table name="COLLATIONS" />
<table name="COLLATION_CHARACTER_SET_APPLICABILITY" />
etc..

Since we don't want a text node we let the tagvalue parameter be null.
Instead we use the xml_attr function to format an xml attribute and
send it in in the attrs parameter.

But what if we need several attributes? You can not write a stored
function with variable number of arguments, so we use the MySql
standard function concat() for this.

select xml_tag('table',null, concat(xml_attr('name',table_name),
xml_attr('engine',engine)),null)
from information_schema.tables
where table_schema = 'INFORMATION_SCHEMA'
gives you:
<table name="CHARACTER_SETS" engine="MEMORY" />
<table name="COLLATIONS" engine="MEMORY" />
<table name="COLLATION_CHARACTER_SET_APPLICABILITY"
engine="MEMORY" />
<table name="COLUMNS" engine="MyISAM" />

And if you want your data as text nodes in sub tags, you do the
same trick with the last parameter in xml_tag, the subtags parameter:

select xml_tag('table',null,null,
concat(xml_tag('name',table_name,null,null),
xml_tag('engine',engine,null,null)))
from information_schema.tables
where table_schema = 'INFORMATION_SCHEMA'
Results in:
<table><name>CHARACTER_SETS</name><engine>MEMORY</engine></table>
<table><name>COLLATIONS</name><engine>MEMORY</engine></table>
<table><name>COLLATION_CHARACTER_SET_APPLICABILITY</name>
<engine>MEMORY</engine></table>
etc..

Of course you can combine attributes, subtags and a text node,
but I leave that for yourself to experiment with.

The stored functions are defined like this:

DELIMITER $$

DROP FUNCTION IF EXISTS `xml_escape` $$
CREATE FUNCTION `xml_escape`( tagvalue VARCHAR(2000))
RETURNS varchar(2000)
BEGIN
IF (tagvalue IS NULL) THEN
RETURN null;
END IF;
RETURN REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(
tagvalue,'&','&amp;'),
'<','&lt;'),
'>','&gt;'),
'"','&quot;'),
'\'','&apos;');
END $$

DROP FUNCTION IF EXISTS `xml_attr` $$
CREATE FUNCTION `xml_attr`(tagname VARCHAR(2000),
tagvalue VARCHAR(2000))
RETURNS varchar(2000)
BEGIN
IF (tagvalue IS NULL) THEN
RETURN null;
END IF;
RETURN CONCAT(' ', tagname ,'="',xml_escape(tagvalue),'" ');
END $$

DROP FUNCTION IF EXISTS `xml_tag` $$
CREATE FUNCTION `xml_tag`(tagname VARCHAR(2000),
tagvalue VARCHAR(2000),
attrs VARCHAR(2000),
subtags VARCHAR(2000))
RETURNS varchar(2000)
BEGIN
DECLARE result VARCHAR(2000);
SET result = CONCAT('<' , tagname);
IF attrs IS NOT NULL THEN
SET result = CONCAT(result,' ', attrs);
END IF;
IF (tagvalue IS NULL AND subtags IS NULL) THEN
RETURN CONCAT(result,' />');
END IF;
RETURN CONCAT(result ,'>',ifnull(xml_escape(tagvalue),''),
ifnull(subtags,''),'</',tagname, '>');
END $$

DELIMITER ;