DELIMITER |

CREATE PROCEDURE myproc ()
  BEGIN
     DECLARE i INT DEFAULT 1;
      DECLARE xml VARCHAR(25) DEFAULT '<a>X</a><a>Y</a><a>Z</a>';
   
    WHILE i < 4 DO
        SELECT xml, i, ExtractValue(xml, '//a[$i]');
        SET i = i+1;
      END WHILE;
   END |

 DELIMITER ;

CALL myproc();
