I have had some comments on my stored functions for XML output
from MySql. First of all, publishning the code as a part of a
blog post was not a good choice. All xml chars, like < and
> must be escaped, and I missed some. I should probably have
used the xml_escape function to precess my text...
That is fixed now, and also I have put the function definitions
on the MySql forge, here. Please do not hesitate to tell me if you
find any errors, or have suggestions for improvement.
Another comment was about the xml functionality of 'mysql --xml'.
Yes, there is a function for generating xml output from MySql, I
do know that. But if you need to output xml from your application
there are a number of problems with it:
1) it has to be invoked from the command line, so you need to
start a command shell, redirect output somewhere etc. This is …
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>
…
A few days ago, Alexander Barkov pushed some changes to the MySQL
5.1 tree that I’ve been waiting to see for some time — variable
support for XPath functions used with ExtractValue()
and UpdateXML(). (This was a fix for Bug
#26518, BTW.) This will be available in MySQL 5.1.20 (or grab the MySQL 5.1 source from
bkbits and build it yourself, if you just can’t wait).
Two slightly different notations are supported, depending on the context, and what sort of checking you want done on the values:
- If you don’t want or need type checking, prefix the variable
name with
$@, like this:$@myvar. However, if you do this, and you make a typo, you’re on …
I have been working on an addition to MySQL the latest month's or
so, adding XML import to MySQL. This weekend I decided that the
version was good enough to submit to MySQL. You can find it at
http://lists.mysql.com/internals/34169. Good enough to submit
yes, probably not good enough for production, but feedback would
be good.
But all in all I am quite satisfied with it.
- focus is on easy-of-use, with automatic matching of tags and
attributes to field names
- support for three different xml formats: using tags for field
values, using attributes or the format used by oth MySQL tools
(not a good format, but anyway..)
- works very much like th CSV file load that has been a feature
of MySQL for very long
I can think of some additions, like support for binary fields
(probably does not work today), better handling of character
encoding etc, improved handling of the SET clause etc. But then,
there are …
zip: