mysql> SET @xml = '<a><b>X</b><b>Y</b></a>';
Query OK, 0 rows affected (0.00 sec)
mysql> SET @i =1, @j = 2;
Query OK, 0 rows affected (0.00 sec)
mysql> SELECT @i, ExtractValue(@xml, '//b[$@i]');
+------+--------------------------------+
| @i | ExtractValue(@xml, '//b[$@i]') |
+------+--------------------------------+
| 1 | X |
+------+--------------------------------+
1 row in set (0.00 sec)
mysql> SELECT @j, ExtractValue(@xml, '//b[$@j]');
+------+--------------------------------+
| @j | ExtractValue(@xml, '//b[$@j]') |
+------+--------------------------------+
| 2 | Y |
+------+--------------------------------+
1 row in set (0.00 sec)
mysql> SELECT @k, ExtractValue(@xml, '//b[$@k]');
+------+--------------------------------+
| @k | ExtractValue(@xml, '//b[$@k]') |
+------+--------------------------------+
| NULL | |
+------+--------------------------------+
1 row in set (0.00 sec)
mysql> DELIMITER |
mysql> 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 |
Query OK, 0 rows affected (0.01 sec)
mysql> DELIMITER ;
mysql> CALL myproc();
+--------------------------+---+------------------------------+
| xml | i | ExtractValue(xml, '//a[$i]') |
+--------------------------+---+------------------------------+
| <a>X</a><a>Y</a><a>Z</a> | 1 | X |
+--------------------------+---+------------------------------+
1 row in set (0.00 sec)
+--------------------------+---+--------------------