MySQL XML解析及數組處理

源碼：

DROP PROCEDURE IF EXISTS `uspUpdateRoleHasRights`;
DELIMITER //
CREATE PROCEDURE `uspUpdateRoleHasRights`(IN xmlData text)
COMMENT
'
<rights>
<roleid>1</roleid>
<rightid>2</rightid>
<rightid>3</rightid>
<rightid>4</rightid>
</rights>
'
BEGIN
select @roleId := EXTRACTVALUE(xmlData,'/rights/roleid');
select @rightIds := EXTRACTVALUE(xmlData,'/rights/rightid');

SET @index = 1;
SET @right_id_count = CHAR_LENGTH(@rightids) - CHAR_LENGTH(REPLACE(@rightIds,' ','')) + 1;
DELETE FROM role_has_rights WHERE roleid = @roleId;
WHILE @index <= @right_id_count
DO
INSERT INTO role_has_rights(roleid,rightid) VALUES(@roleId,SUBSTRING_INDEX(SUBSTRING_INDEX(@rightIds,' ',@index),' ',-1));
SET @index = @index + 1;
END WHILE;

END //
DELIMITER ;

測試：

 call uspUpdateRoleHasRights('
 <rights>
<roleid>1</roleid>
<rightid>1</rightid>
<rightid>2</rightid>
<rightid>3</rightid>
<rightid>4</rightid>
<rightid>5</rightid>
<rightid>6</rightid>
<rightid>7</rightid>
<rightid>8</rightid>
<rightid>9</rightid>
<rightid>10</rightid>
<rightid>11</rightid>
<rightid>12</rightid>
<rightid>13</rightid>
<rightid>14</rightid>
<rightid>15</rightid>
<rightid>16</rightid>
<rightid>17</rightid>
<rightid>18</rightid>
<rightid>19</rightid>
<rightid>20</rightid>
<rightid>21</rightid>
<rightid>22</rightid>
<rightid>23</rightid>
</rights>
 ');