MYSQL UDF function to return XML

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 INVOKERBEGIN    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 helpGreetings