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

 

C# | Winforms | Textbox within Button

Here is an example for creating a Button control which contains a Textbox inside it.
This is very usefull when the button functionality is based (only) on the textbox content text, and it is very usefull for the user.

Screenshot (image) example:

The Code:

class ButtonWithTextbox : Button
{
    TextBox _textbox = new TextBox();

    public int TextboxWidth
    {
        get { return _textbox.Width; }
        set { _textbox.Width = value; }
    }
    public Point TextboxLocation
    {
        get { return _textbox.Location; }
        set { _textbox.Location = value; }
    }
    /** And we may add all properties of the Textbox ***/

    public ButtonWithTextbox()
    {
        this.Controls.Add(_textbox);
    }
}

Also work great on the designer... :)

MDB-Blog
http://mdb-blog.blogspot.com