50% OFF!!!

Booking.com

Sunday, February 10, 2013

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 = '      ';

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 :)

 

No comments:

Post a Comment