|
|
|
title : XML element extraction using MySQL 5
story : Suppose you have a table in MySQL 5 where you store
a complete XML document in one field and you want to
extract a particular element from that document in
order to display or sort by etc. Here is a stored
procedure which does the job for you on the database
level.
author : Ioannis Cherouvim
web : http://cherouvim.com
date : 2005-12-03
we need to have log_bin_trust_routine_creators set to 1 for this to work
| | SET GLOBAL log_bin_trust_routine_creators = 1; | |
The stored procedure. Works for a table 'people' and is called 'tagextract'.
Takes as arguments the xml document and the element name to extract.
Notice that here I've used varchar(512) for the xml document. Change type
if necessary.
| DELIMITER $$
DROP FUNCTION IF EXISTS `people`.`tagextract` $$
CREATE FUNCTION `tagextract`(xml varchar(512), tag varchar(64)) RETURNS varchar(512)
BEGIN
DECLARE startTag varchar(66);
DECLARE endTag varchar(67);
DECLARE start int;
DECLARE end int;
SET startTag = CONCAT('<', tag, '>');
SET endTag = CONCAT('</', tag, '>');
SET start = LOCATE(startTag, xml) + LENGTH(startTag);
SET end = LOCATE(endTag, xml) - start;
return SUBSTRING(xml, start, end);
END $$
DELIMITER ; | |
Demonstration
Here is the table people which has id, name and an xml document
| SELECT * FROM people
1, 'john', '<person><job>clerc</job><weight>55</weight></person>'
2, 'nick', '<person><job>student</job><weight>134</weight></person>'
3, 'bob', '<person><job>boxer</job><weight>42</weight></person>'
4, 'jim', '<person><job>pianist</job><weight>3</weight></person>' | |
Now we want to extract the job element from every row and sort using by job
| SELECT *, tagextract(people.xml, "job") as job FROM people order by job
id, age, name, xml, job
3, 'bob', '<person><job>boxer</job><weight>42</weight></person>', 'boxer'
1, 'john', '<person><job>clerc</job><weight>55</weight></person>', 'clerc'
4, 'jim', '<person><job>pianist</job><weight>3</weight></person>', 'pianist'
2, 'nick', '<person><job>student</job><weight>134</weight></person>', 'student' | |
|
|
| MySQL or SQL Query to XML Output
Categories : PHP, MySQL, XML, Databases | | | This program allows you to upload an ODBC ressource - i.e. an MS-Access database to a MySQL server.
Categories : Databases, MySQL, Complete Programs, PHP, Databases | | | DBXML- A Class to backup databases in XML Format using web interface
Categories : PHP, PHP Classes, Databases, MySQL, XML | | | TAB_STRUCT Class: Is supporting Class for the DBXML Class
Categories : PHP, PHP Classes, MySQL, XML, Databases | | | bookmarker - PHP, PHPLIB, MySQL WWW based bookmark manager
Categories : MySQL, PHP, MySQL, Complete Programs, Databases | | | Accepts a database & hostname from a user and then HTTP username and password. Uses this to connect to a MySQL database. Produces a form based on the tables it finds there to allow the user to do SELECTs, INSERTs, and DELETEs.
Categories : Databases, PHP, MySQL, Complete Programs | | | phpAds, a complete banner and ad management system with detailled tracking and stats.
Categories : MySQL, Complete Programs, Ecommerce, PHP, Databases | | | Point and Click Interface ala MS Access for creating SQL statements.
Categories : MySQL, Complete Programs, General SQL, PHP, Databases | | | MySQL wrapper class (PHP 5+ only)
Categories : MySQL, Databases, Classes and Objects, Object Oriented | | | Message of the Day - Random Message (Needs MySQL!)
Categories : Databases, HTML and PHP, PHP, MySQL | | | email new items in db
Categories : PHP, Email, Databases, MySQL, Beginner Guides | | | Alternating background color for HTML table rows
Categories : PHP, Databases, MySQL, HTML and PHP | | | color codes for positive and negative numbers
Categories : PHP, MySQL, Databases, HTML | | | Authorize Me! An authentication script.
Categories : MySQL, Databases, Authentication, PHP | | | A very simple way to build and do a hierarchical html categories browser without javascript , just using html php and mySql
Categories : HTML and PHP, Databases, Algorithms, PHP, MySQL | |
| | | | K C wrote : 1367
This is an interesting example but imho should be taken with a pinch of salt. Firstly, the start tag cannot deal with having any attributes set in it, because the autogenerated tag is "<tagname>", and secondly, if one happens to nest two tags of the same type within each other, the end tag detector will (presumably) pick up the opening tag of the first pair and the closing tag of the second (inner) pair.
To solve the first problem one would need (very expensive) regular expressions, or at least a "find me the next ">" after "<tagname". To solve the second problem one would presumably need some kind of SQL-level XML parser.
| | | | Ioannis Cherouvim wrote :1368
Thanks for your comment. You are absolutely correct. This is a very simple stored procedure which was designed to solve this very simple scenario. I`ve used it on a real world problem and it works great. Previously I had to get all the rows, convert them to some type of Comparable datastructure and sort (or do anything else) on the programming language level and not on the database level.
When I move to something big such as oracle, which I think supports these kind of datastructure manipulations directly inside the DB, things will be simpler!
| |
|
|
|
