Downloads

Information

A library of functions to map relational data to the XML format. XML is a principal format next to JSON for data exchange in AJAX architectures.

about

MySQL UDFs offer a powerful way to extend the functionality of your MySQL database.

The UDFs on this site are all free: free to use, free to distribute, free to modify and free of charge. Even for commercial projects.

See also

The lib_mysqludf_xql library provides an array of functions, which can be used to create XML output directly from MySQL using a single SQL query. This prevents having to convert a MySQL result to XML in a PHP script, Ruby script, etc. This improves performance and simplifies your code.

The library provides the same functionality as SQL/XML, seen in MS SQL server and Oracle. However it uses normal functions. By using subqueries the same results as SQL/XML can be generated.

lib_mysqludf_xql uses the xmlwriter library of libxml2, which is a superfast non-cached, forward-only generator of XML data. This library automaticly escapes XML entities and dynamicly allocated memory as the XML content grows.

Build / Install

Preparation

Login as root
Install libxml2 (`apt-get install libxml2-dev` or http://www.libxml.com)
Download and and unpack lib_mysqludf_xql-*.tar.gz
Change dir to lib_mysqludf_xql source directory

Build and full install

./configure [--with-mysql=<path/to/mysql/bin/mysql_config>]
make
[sudo] make install

This procedure should work for most MySQL 5.0 and earlier installations. MySQL 5.1 user (as of MySQL 5.1.19) must ensure that the lib_mysqludf_xql.so library is avalailable from the plugin_dir. The location of the plugin dir defaults to: <mysql-home>/lib/mysql/ but can be configured to a custom location in the my.cnf.

API

lib_mysqludf_xql_info ( )
Output the library version.
xql_textdecl ( string version, string encoding [, string standalone] )
Write a text declarion; <?xml version="1.0" encoding="iso-8859-1" standalone="yes"?>
xql_pi ( string content AS target )
Write processing instruction; <?xsl-stylesheet type="text/xml" href="yourstyle.xsl"?>
xql_dtd ( string name [, string pubid, string sysid [, string subset]]] )
Write a DTD tag
xql_element ( string name, [string content | string xml AS `xql:%` [, string att_val AS `att_name`, ...]] )
Write element node with attributes.
xql_forest ( [string name_root AS `xql:root`,] string content AS name | string xml, ... )
Write an element for each name/content pair.
Arguments starting with `xql:` as name or output of xql functions are inserted as raw XML.
xql_concat ( [string name_root AS `xql:root`,] string xml, ... )
Concatenate xml.
xql_agg ( [string name_root AS `xql:root`,] [string name_child AS `xql:child`,] string content AS name | string xml, ... )
Group concatenate XML for each row.
Normal arguments are inserted as XML nodes. Arguments starting with `xql:` as name or output of xql functions are inserted as raw XML.
xql_comment ( string content )
Write comment node
xql_cdata ( string content )
Write CDATA section node
xql_encode ( string content )
Encode special chars

script examples

PHP implementation - mysql extension

$result = mysql_query("SELECT xql_agg('root' AS `xql:root`, xql_element('node', description)) FROM `mytable`");
$xml = mysql_result($result, 0);

PHP implementation - mysqli extension

$result = $db->query("SELECT xql_agg('root' AS `xql:root`, xql_element('node', description)) FROM `mytable`");
list($xml) = $result->fetch_row();

query examples

Simple flat result

SELECT xql_agg('classes' AS `xql:root`, xql_element('class', null, concat(grade, suffix) AS name, teacher)) FROM schoolclass; 
<classes>
 <class name="1A" teacher="Pullen"/>
 <class name="2A" teacher="Berg"/>
 <class name="3A" teacher="Heijden"/>
 <class name="3B" teacher="Ven"/>
 <class name="4A" teacher="Theunissen"/>
 <class name="4B" teacher="Huizingh"/>
 <class name="4C" teacher="Groot"/>
 <class name="5A" teacher="Brongers"/>
 <class name="5B" teacher="Zijlstra"/>
 <class name="6A" teacher="Brommer"/>
 <class name="7A" teacher="Nimwegen"/>
 <class name="7B" teacher="Lede"/>
 <class name="8A" teacher="Spaansen"/>
 <class name="8B" teacher="Wiersma"/>
</classes>

Simple tree result

SELECT xql_agg('classes' AS `xql:root`, `schoolclasses` AS `xql:raw`) FROM
(SELECT xql_element('class', xql_agg('pupil' AS `xql:child`, firstname, lastname)), concat(grade, suffix) AS `name`, teacher) AS `schoolclasses` FROM schoolclass LEFT JOIN pupil ON schoolclass.id = pupil.schoolclass_id WHERE grade = 3 GROUP BY schoolclass.id) AS schoolclass 
<classes>
 <class name="3A" teacher="Heijden">
  <pupil><firstname>Toon</firstname><lastname>Baars</lastname></pupil>
  <pupil><firstname>Guido</firstname><lastname>Nas</lastname></pupil>
  <pupil><firstname>Steven</firstname><lastname>Schijndel</lastname></pupil>
  <pupil><firstname>Renz</firstname><lastname>Pijnenborgh</lastname></pupil>
  <pupil><firstname>Cees</firstname><lastname>Star</lastname></pupil>
  <pupil><firstname>Ellen</firstname><lastname>Gommers</lastname></pupil>
  <pupil><firstname>Pim</firstname><lastname>Thoonen</lastname></pupil>
  <pupil><firstname>Jet</firstname><lastname>Lasseur</lastname></pupil>
  <pupil><firstname>Lucy</firstname><lastname>Donker</lastname></pupil>
  <pupil><firstname>Richard</firstname><lastname>Bosman</lastname></pupil>
  <pupil><firstname>Roelie</firstname><lastname>Pol</lastname></pupil>
  <pupil><firstname>Hans</firstname><lastname>Stralen</lastname></pupil>
  <pupil><firstname>Marcel</firstname><lastname>Rademaker</lastname></pupil>
  <pupil><firstname>Mark</firstname><lastname>Pessel</lastname></pupil>
  <pupil><firstname>Kees</firstname><lastname>Blonk</lastname></pupil>
  <pupil><firstname>Christy</firstname><lastname>Dijk</lastname></pupil>
  <pupil><firstname>Herman</firstname><lastname>Vroome</lastname></pupil>
  <pupil><firstname>Nina</firstname><lastname>Viegers</lastname></pupil>
  <pupil><firstname>Jan</firstname><lastname>Logtenberg</lastname></pupil>
  <pupil><firstname>Test</firstname><lastname>Test</lastname></pupil>
  <pupil><firstname>Frits</firstname><lastname>Schaafsma</lastname></pupil>
  <pupil><firstname>Willem</firstname><lastname>Ostendorf</lastname></pupil>
  <pupil><firstname>Peter</firstname><lastname>Boorn</lastname></pupil>
 </class>
 <class name="3B" teacher="Ven">
  <pupil><firstname>Bas</firstname><lastname>Govers</lastname></pupil>
  <pupil><firstname>Nico</firstname><lastname>Pol</lastname></pupil>
  <pupil><firstname>Adolf</firstname><lastname>Lozeman</lastname></pupil>
  <pupil><firstname>Albert</firstname><lastname>Visch</lastname></pupil>
  <pupil><firstname>Reitte</firstname><lastname>Scholderman</lastname></pupil>
  <pupil><firstname>Hellen</firstname><lastname>Batterink</lastname></pupil>
  <pupil><firstname>Syb</firstname><lastname>Hoekstra</lastname></pupil>
  <pupil><firstname>Ger</firstname><lastname>beijers</lastname></pupil>
  <pupil><firstname>Lisa</firstname><lastname>Martin</lastname></pupil>
  <pupil><firstname>Erik</firstname><lastname>Scheijndel</lastname></pupil>
  <pupil><firstname>Rob</firstname><lastname>Hazelaar</lastname></pupil>
  <pupil><firstname>Theo</firstname><lastname>Veldboer</lastname></pupil>
  <pupil><firstname>Jos</firstname><lastname>Drost</lastname></pupil>
  <pupil><firstname>M.</firstname><lastname>Dijkshorn</lastname></pupil>
  <pupil><firstname>Sjaak</firstname><lastname>Verzijl</lastname></pupil>
  <pupil><firstname>Nina</firstname><lastname>Kremers</lastname></pupil>
  <pupil><firstname>Monique</firstname><lastname>Weesie</lastname></pupil>
  <pupil><firstname>Jorien</firstname><lastname>Smits</lastname></pupil>
  <pupil><firstname>Jos</firstname><lastname>Berg</lastname></pupil>
  <pupil><firstname>Herman</firstname><lastname>Eldering</lastname></pupil>
  <pupil><firstname>Floris</firstname><lastname>Droogh</lastname></pupil>
  <pupil><firstname>Douwe</firstname><lastname>Verburg</lastname></pupil>
  <pupil><firstname>Ton</firstname><lastname>Lamers</lastname></pupil>
  <pupil><firstname>Ronald</firstname><lastname>Koopmanschap</lastname></pupil>
  <pupil><firstname>Fred</firstname><lastname>Weijde</lastname></pupil>
  <pupil><firstname>Vincent</firstname><lastname>Rodijk</lastname></pupil>
  <pupil><firstname>Els</firstname><lastname>Koene-van Leeuwen</lastname></pupil>
 </class>
</classes>