XML met MySQL
Uit Yapf
XML is het buzzwordformaat van de 21e eeuw maar vreemdgenoeg is MySQL een van de weinig databases die er nog steeds niets mee kan.
Omdat het toch praktisch een vereiste functionaliteit is, mede omdat de PoestgreSQL omgeving er moeiteloos mee werkt, heb ik me laten inspireren op werk van anderen om een aantal van PostgreSQL's XMLfuncties te poorten naar MySQL.
Hoe zit het met het inlezen van XML? Dat vereist een XML parser en gaat veel te ver voor dit artikeltje.
Inhoud |
De functies
XMLELEMENT()
XMLELEMENT(naam, attributen, inhoud)
Genereert één element op basis van de naam, attributen en inhoud.
In PostgreSQL geef je aan XMLELEMENT() een variabel aantal parameters mee; een naam, een XMLATTRIBUTES() en een serie waarden of andere elementen.
MySQL kan geen arrays of recordsets doorgeven als parameter van een stored-*, dus zit je vast aan precies drie parameters. Als je meer dan één attribuut wilt meegeven dan moet je die eerst aan elkaar concatten tot één string, en hetzelfde geldt voor de nodes/waarden.
XMLATTRIBUTE()
XMLATTRIBUTE(naam, waarde)
Genereert een attribuut-string zoals die in een xml-element wordt gebruikt: naam="waarde"
In PostgreSQL kun je hier een serie key-value pairs doorgeven maar omdat MYSQL dat niet kan moet je in MySQL een serie geconcatteneerde XMLATTRIBUTE() aan XMLELEMENT geven.
XMLCDATA()
XMLCDATA(waarde)
Dit voorziet de data van een CDATA tag zodat XML de inhoud niet zal proberen te verwerken.
XMLESCAPE()
Dit is een interne functie die een aantal tekens van de aangeleverde data vervangt om ze veilig te maken voor gebruik in XML.
Voorbeelden
Hello world
SELECT xmlelement('page','','waarde');
geeft:
<page>waarde</page>
Hello world met een attribuut
SELECT xmlelement('page',xmlattribute('worldtype',2),'waarde');
geeft:
<page worldtype="2" >waarde</page>
Hello world met een subelement
SELECT xmlelement('page',xmlattribute('worldtype',2),xmlelement('titel','','Goedemorgen Nederland'));
geeft:
<page worldtype="2" ><titel>Goedemorgen Nederland</titel></page>
Data uit een tabel
Dit voorbeeld haalt data uit een (virtuele) tabel, plakt twee attributen aan elkaar voor de page tag, en plakt twee elementen aan elkaar voor de inhoud van de page tag.
SELECT xmlelement('page', CONCAT(xmlattribute('voornaam', achternaam),xmlattribute('achernaam', achternaam)), CONCAT(xmlelement('subtag','','Dit is mijn eerste tag.'),xmlelement('subtag','','plopjes'))) FROM (SELECT 'henk' AS voornaam,'hatseflats' AS achternaam) AS people
geeft:
<page voornaam="hatseflats" achternaam="hatseflats" > <subtag>Dit is mijn eerste tag.</subtag> <subtag>plopjes</subtag> </page>
Meerdere records uit een tabel combineren
Om meerdere records te comineren tot één tag zul je met GROUP_CONCAT() aan de slag moeten. Vergeet niet de SEPARATOR op een lege string te zetten, anders krijg je comma's ertussen.
SELECT XMLELEMENT('personen','', GROUP_CONCAT( xmlelement('persoon', '', CONCAT(xmlelement('voornaam','',voornaam),xmlelement('achternaam','',achternaam))) SEPARATOR '')) FROM ( (SELECT 'jan' AS voornaam,'pieleman' AS achternaam) UNION (SELECT 'kees' AS voornaam,'watervrees' AS achternaam)) AS people
De functiedefinities
CREATE FUNCTION `xmlelement`( p_tagname TEXT, p_attributes TEXT, p_value TEXT ) RETURNS text CHARSET latin1 NOT DETERMINISTIC CONTAINS SQL SQL SECURITY DEFINER COMMENT '' BEGIN IF p_attributes <> '' THEN SET p_attributes = CONCAT(' ',p_attributes); END IF; SET p_value = TRIM(p_value); IF (LEFT(p_value, 1)<> '<' OR RIGHT(p_value, 1) <> '>') THEN SET p_value = xmlescape(p_value); END IF; RETURN CONCAT('<',p_tagname,p_attributes,'>',p_value,'</',p_tagname,'>'); END; CREATE FUNCTION `xmlattribute`( p_name TEXT, p_value TEXT ) RETURNS text CHARSET latin1 NOT DETERMINISTIC CONTAINS SQL SQL SECURITY DEFINER COMMENT '' BEGIN RETURN CONCAT(' ',p_name,'="',xmlescape(p_value),'" '); END; CREATE FUNCTION `xmlcdata`( tagvalue TEXT ) RETURNS varchar(2000) CHARSET latin1 NOT DETERMINISTIC CONTAINS SQL SQL SECURITY DEFINER COMMENT '' BEGIN RETURN CONCAT('<![CDATA[', tagvalue,']]>'); END; CREATE FUNCTION `xmlescape`( tagvalue TEXT ) RETURNS varchar(2000) CHARSET latin1 NOT DETERMINISTIC CONTAINS SQL SQL SECURITY DEFINER COMMENT '' BEGIN IF (tagvalue IS NULL) THEN RETURN NULL; END IF; RETURN REPLACE(REPLACE(REPLACE(REPLACE(REPLACE( tagvalue,'&','&'), '<','<'), '>','>'), '"','"'), '\'','''); END;