MySQL xpath ExtractValue with delimiter

Hello,

I recently noticed the XPATH option inside MySQL.

What a wonderfull way for handling XML. (http://dev.mysql.com/doc/refman/5.1/en/xml-functions.html)

I used ExtractFunction, and noticed that this function is space delimited:

Because ExtractValue() returns multiple matches as a single space-delimited string

So I needed to create the same function with custom text delimted (comma, semi-colon or anything you want).

The function called: EXTRACTVALUE_ALL which is the same as EXTRACTVALUE just get a delimiter text to seperate between results. (seperated text).
Tested on MySQL 5.5.28.

 

Create Function CODE:

DROP FUNCTION IF EXISTS EXTRACTVALUE_ALL;


DELIMITER |
CREATE FUNCTION EXTRACTVALUE_ALL(p_xml TEXT, p_xpathExpr TEXT, p_delimiter TEXT) RETURNS TEXT
BEGIN
DECLARE total_elements INT;
DECLARE xpath_expression_count, xpath_expression_index  TEXT;
DECLARE single_tag, result  TEXT;

# calc TOTAL elements
SET xpath_expression_count = CONCAT('count(', p_xpathExpr, ')');
SELECT EXTRACTVALUE(p_xml, xpath_expression_count) INTO total_elements;

# run over elements (create long text)
SET result = '';
SET xpath_expression_index = CONCAT(p_xpathExpr, '[$@i]');
SET @i = 1;
WHILE @i <= total_elements DO
SET single_tag = EXTRACTVALUE(p_xml, xpath_expression_index);
SET result = IF(result='', single_tag, CONCAT(result, p_delimiter, single_tag));
SET @i = @i + 1;
END WHILE;

# return total result
RETURN result;
END |

Usage Code:

SET @XXXMMMMLLL = '  time1   time2 ';

SELECT EXTRACTVALUE_ALL(@XXXMMMMLLL, '/descendant-or-self::time', '  |  ');

Output:

time1  |  time2

Rememeber that you will need a MYSQL permission for creating new functions.

MDB-BLOG :)