MYSQL UDF function to return XML

Question

Situation:

I want to create a mysql function named XMLify, that takes in a string and an expression that will return a set

XMLify(string, expr)

The function should wrap each returned field of each returned row in the set, into its own XML tag. The name of the tag should be the field name.

Small example:

select XMLify('foo', (SELECT 1 as `a`, 2 as `b` UNION SELECT 3 as `a`, 4 as `b`));

should return:

<foo><a>1</a><b>2</b></foo><foo><a>3</a><b>4</b></foo>

I want to have this, because it will enable me to run a complex query with many joins and/or dependant subqueries, without having to return redundant data to the client.

I already have a work-around without the function I want to build. But this involves writing difficult queries that are not easily maintained. See my examples below.

Making sure the field names are legal XML node name is for a later worry. Once the function stands, I will think of some algorithm that will take the field name and turn it into some legal XML node name.

Also escaping the XML data is for a later worry. This will be done with a different function named CDATAify, that will simply wrap all data into <![CDATA[ and ]]>, and will escape any prior occurance of ]]> in the data into ]]]]><![CDATA[>.

I haven't been able to accomplish this using stored functions in MySQL, because these do not take in resultsets. Also, even if you were to pass in SQL as a string, and then prepare statement and execute it, you cannot access the fields if you don't already know the field names.

So now I am wondering if the trick can be done with user defined functions (UDF). This is something I haven't yet worked with, and I would like your advise here before enbarqueing.

QUESTIONS:

So my questions now are:

• To recap, I would like to have a MySQL function that I can pass an expression or result set, and where I can also use the field names of the result set.
• Am I assuming correct that this will not be possible in stored functions?
• Will UDF take in expessions / their result set as an argument?
• Will UDF allow me to to access field names of the result set, so I can use them as the XML tag names
• Will it work on Windows as well? I read that UDF have some limitations
• Is there a better way I haven't thought of yet?
• Will I be able to have a UDF .dll that I can create on my own development computer and then copy the .dll file to my server and use it there?
• How do I get this show on a roll? Please be thorough and take into account that I have MySQL 5.5 64-bit on a Windows computer.

EXAMPLE:

Imagine having the following 3 tables:

users:         grades:             toys:
+----+------+  +--------+-------+  +--------+--------------+
| id | name |  | userid | grade |  | userid | toy          |
+----+------+  +--------+-------+  +--------+--------------+
|  1 | Bart |  |      1 |     E |  |      1 | slingshot    |
|  2 | Lisa |  |      1 |     E |  |      1 | Krusty       |
| .. | ...  |  |      2 |     A |  |      2 | Malibu Stacy |  
| .. | ...  |  |      2 |     B |  |      2 | calculator   |
+----+------+  +--------+-------+  +--------+--------------+

My desired result would be, limited to Bart and Lisa:

<users>
    <user>
        <id><![CDATA[1]]></id>
        <name><![CDATA[Bart]]></name>
        <grades>
            <grade><![CDATA[E]]></grade>
            <grade><![CDATA[E]]></grade>
        </grades>
        <toys>
            <toy><![CDATA[slingshot]]></toy>
            <toy><![CDATA[Krusty]]></toy>
        </toys>
    </user>
    <user>
        <id><![CDATA[1]]></id>
        <name><![CDATA[Lisa]]></name>
        <grades>
            <grade><![CDATA[A]]></grade>
            <grade><![CDATA[B]]></grade>
        </grades>
        <toys>
            <toy><![CDATA[Malibu Stacey]]></toy>
            <toy><![CDATA[calculator]]></toy>
        </toys>
    </user>
</users>

Consideration:

• I don't want in PHP or C# to have to first query the user table, and then per user run two additional queries for the grades and toys. Because for 1000 users, I would be be running 2001 queries.
• I also don't want to run a query with all joins and go through the result set in PHP or C#, because the user name would be sent as many times as the number of grades times the number of toys. Imagine having a user field containing a huge blob!
• I cannot simply use GROUP_CONCAT on joined tables, as the grades/toys would still appear double.
• And if I would use GROUP_CONCAT with DISTINCT, I will lose the grades with are the same, such as Bart's two E's.

So currently I would use the following statement to get this result, involving two dependant subqueries. This works great:

SELECT
    CONCAT(
        '<users>',
            IFNULL(
                GROUP_CONCAT(
                    '<user>',
                        '<id><![CDATA[',
                            REPLACE(u.id,']]>',']]]]><![CDATA[>'),
                        ']]></id>',
                        '<name><![CDATA[',
                            REPLACE(u.name,']]>',']]]]><![CDATA[>'),
                        ']]></name>',
                        '<grades>',
                            (
                                SELECT
                                    IFNULL(
                                        GROUP_CONCAT(
                                            '<grade><![CDATA[',
                                                REPLACE(g.grade,']]>',']]]]><![CDATA[>'),
                                            ']]></grade>'
                                            SEPARATOR ''
                                        ),
                                    '')
                                FROM
                                    grades g
                                WHERE
                                    g.userid = u.id
                            ),
                        '</grades>',
                        '<toys>',
                            (
                                SELECT
                                    IFNULL(
                                        GROUP_CONCAT(
                                            '<toys><![CDATA[',
                                                REPLACE(t.toy,']]>',']]]]><![CDATA[>'),
                                            ']]></toys>'
                                            SEPARATOR ''
                                        ),
                                    '')
                                FROM
                                    toys t
                                WHERE
                                    t.userid = u.id
                            ),
                        '</toys>',
                    '</user>'
                    SEPARATOR ''
                ),
                ''
            ),
        '</users>'
    )
FROM
    users u
WHERE
    u.name = 'Bart' or u.name = 'Lisa'
;

Now as you might notice, it is a rather big and ugly query, which hurts the eyes when reading. Maintaining such a query is hard. If I would have my functions XMLify and CDATAify, I could simply write this instead:

SELECT
    XMLify('users',(
        XMLify('user',(
            SELECT
                CDATAify(u.id) as id,
                CDATAify(u.name) as name,
                XMLify('grade',(
                    SELECT
                        CDATAify(g.grade) as grade
                    FROM
                        grades g
                    where
                        g.userid = u.id
                )) AS grades,
                XMLify('toys',(
                    SELECT
                        CDATAify(t.toy) as toy
                    FROM
                        toys t
                    where
                        t.userid = u.id
                )) AS grades
            FROM
                users u
            WHERE
                u.name = 'Bart' or u.name = 'Lisa'
        ))
    ))
;

EDIT:

As mentioned in the comments by N.B., there is a repository on Github, possibly holding all I need. I have however spent several days now just trying to get this to work on my system, without success. Any answer that holds a step-by-step how-to on how to install this on my MySQL 5.5 64-bit server running on Windows is also acceptable.

Please take into account that I have no experience with makes, makefiles, etc. So please be thorough in explaining.

Answer 1

x, just today I found this question, I just hope to answer this question not (too) late and if it is too late, maybe it will help someone else.

Cause MySql doesn't allow to implement dynamics queries on functions or triggers I just choose to implement a stored procedure.

DELIMITER //
DROP PROCEDURE IF EXISTS XMLify//
CREATE PROCEDURE XMLify(IN wraper VARCHAR(100), IN expr VARCHAR(1000))
LANGUAGE SQL NOT DETERMINISTIC READS SQL DATA SQL SECURITY INVOKER
BEGIN
    DECLARE done INT DEFAULT FALSE;
    DECLARE col_name VARCHAR(255);
    DECLARE cur1 CURSOR FOR
    SELECT
        column_name
    FROM
        information_schema.columns
    WHERE
        table_schema = 'test' AND /*Name of the database (schema)*/
        table_name = 'temp' AND
        column_name <> 'c4l5mn';
    DECLARE CONTINUE HANDLER FOR NOT FOUND SET done = TRUE;

    DROP TABLE IF EXISTS temp;
    SET @SQL = CONCAT('CREATE TABLE temp (c4l5mn TINYINT NOT NULL DEFAULT ''1'') AS ', expr);
    PREPARE stmt1 FROM @SQL;
    EXECUTE stmt1;
    DEALLOCATE PREPARE stmt1;

    OPEN cur1;
    SET col_name = '';
    SET @SQL = '';
    read_loop: LOOP
        FETCH cur1 INTO col_name;
        IF done THEN
          LEAVE read_loop;
        END IF;
        SET @SQL = CONCAT(@SQL, '<', col_name, '>'', ', col_name, ', ''</', col_name, '>');
    END LOOP;
    CLOSE cur1;
    SET @SQl = CONCAT('SELECT GROUP_CONCAT(CONCAT(''<', wraper, '>', @SQL, '</', wraper, '>'') SEPARATOR '''') row FROM temp GROUP BY c4l5mn');
    PREPARE stmt1 FROM @SQL;
    EXECUTE stmt1;
    DEALLOCATE PREPARE stmt1;
    DROP TABLE IF EXISTS temp;
END//
DELIMITER ;

that's it, now you can call it just like

CALL XMLify('foo', 'SELECT 1 as `a`, 2 as `b` UNION SELECT 3, 4');

And it will return

<foo><a>1</a><b>2</b></foo><foo><a>3</a><b>4</b></foo>

Call

CALL XMLify('foo', 'SELECT 1 as a, 2 as b, 3 as c UNION SELECT 4, 5, 6');

Will return

<foo><a>1</a><b>2</b><c>3</c></foo><foo><a>4</a><b>5</b><c>6</c></foo>

I just hope it would help Greetings