Friday, July 13, 2007

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 ;

18 comments:

Unknown said...

Hi Erik!

Please consider adding your fine functions to the code snippet repository on MySQL Forge. Great stuff!

http://forge.mysql.com/snippets/

Scott said...

Or just go to the command line and do

mysql --xml -e "your query here"

jbalint said...

Shouldn't that second-to-last line of xml_tag() include the closing tag? Such as:

RETURN CONCAT(result ,'>',ifnull(xml_escape(tagvalue),''),ifnull(subtags,''),'</', tagname, '>');

Giuseppe Maxia said...

It is not in MySQL's SQL dialect, but the command line client can do XML on the fly.

mysql -X -e 'select * from test.foo'
<?xml version="1.0"?>
<resultset statement="select * from test.foo">
<row>
<field name="foo">1</field>
<field name="bar">abc</field>
</row>
<row>
<field name="foo">2</field>
<field name="bar">def</field>
</row>
<row>
<field name="foo">3</field>
<field name="bar">ghi</field>
</row>
</resultset>

Erik said...

The functions are now available at http://forge.mysql.com/snippets/view.php?id=86
I have also corrected the error jess pointed out.

The mysql --xml is an alternative, but you can not control the xml format and you need to create a command shell to use it.

Unknown said...

The snippets link is broken, it should be http://forge.mysql.com/tools/tool.php?id=86

Can I suggest the following change to xml_tag() :

Argument : nsPrefix VARCHAR(20)

Code :
IF nsPrefix IS NOT NULL THEN
SET tagname = concat(nsPrefix, ':', tagname);
END IF;

And to xml_escape() bold portion:

IF (tagvalue IS NULL) THEN
RETURN NULL;
ELSEIF (tagvalue regexp '\'|"|&|<|>') = 0 THEN
RETURN tagvalue;

END IF;

dbunic said...

Hi,
just to add another approach to the XML creation. I wrote PHP XML generator in less than 80 lines. It accepts SQL and outputs XML. You can even describe hierarchy with optional parameter. If you are interested, please visit www.redips.net | From MySQL to XML with PHP
Thank you!

AJ's bakwas said...

hi

I have stored xml files in the mysql db..table name mst_x in a blob datatype..now I want to retrieve it however
SELECT * FROM mst_x\G command is giving error.. I am using version 5.1.31community

Unknown said...

Hello Erik
I do not know if this is the right place to post this question??
I am having a problem the next code is not working and I can not figure out why.

------------------------------------

$sql->query("SELECT col1, col2, col3 FROM table WHERE col4='a_value' ORDER BY id DESC LIMIT 20");



$xml = '';
$xml .= '';

while ($row=$sql->fetch_row()) {

$col1 = $row['col1'];
$col2 = $row['col2'];
$col3 = $row['col3'];


$col1 = htmlspecialchars($col1);
$col2 = htmlspecialchars($col2);


$xml .= '';
$xml .= '' . $col1 . '';
$xml .= '' . $col2 . '';
$xml .= '' . $col3 . '';
$xml .= '';

}

$xml .= '';
echo $xml;


--------------------------------------

Without the WHERE statement it works fine but with the WHERE statement in the query
I get the next error from the feed validator (http://feedvalidator.org)

XML parsing error: :1:1: not well-formed (invalid token)

I am not well known to xml perhaps you have answer ???
With regards John J

Erik said...

Hi Hans,
Looks like php code. I'm not really an expert on this and haven't used php for years, but I notice that you don't call htmlspecialchars($col3). Could that be the problem? If the where clause gives you data where col3 contains special chars? You could try it.

Arne said...

Thanks Erik,
I think you just solved a nasty problem for me.

-Arne

thara said...

Thanks a lot. It was very useful.

thara said...

Thanks a lot. It was very useful for me.

Unknown said...

here is better version of UpdateXML function:


DELIMITER |

create FUNCTION UpdateOrInsertXML(s text, xKey text, xVal text)
RETURNS text
begin
DECLARE r text;

IF s like concat('%<',xKey,'>%%') THEN
SET r = UpdateXML(s, concat('//',xKey), concat('<',xKey,'>',xml_escape(xVal),'')) ;
ELSE SET r = concat( left(s, LOCATE('>', s) ),'<',xKey,'>',xml_escape(xVal),'', substring(s, LOCATE('>', s)+1 ) );
END IF;

RETURN r;
end
|
DELIMITER ;


this function will create XML element if element do not exist, it work only on flat XML

Tom said...

Any chance that the source for the xml_attr functions is available on sourceforge somewhere...? I followed the link and it's broken...

Tom said...

Hi Erik,

Any chance you could find the link to the source for this module?

The two in the comments are broken. Perhaps you need to specify the project name you saved it under so that it can be searched for rather than specifying a link with an id...

Thanks - it looks like it could solve my MS SQL/MySql portability issue...

Cheers,
Tom

Erik said...

Hi Tom,
Quite an old post this..

The code for the stored procedures is actually in the original post, at the end, just before the comments.

Erik

Tom said...

Apologies - I saw the references to another site for the source code and that led me to believe it might be a C extension of MySql... Feel free to delete my comment as it may only serve to perpetuate the myth! Thanks for posting this - it's just what I need to become Sql Server/MySql portable... :)