DELIMITER |
DROP PROCEDURE IF EXISTS xmldump_load |
CREATE PROCEDURE xmldump_load(file_name VARCHAR(128),
database_name VARCHAR(128),
table_name VARCHAR(128))
BEGIN
DECLARE xml TEXT;
DECLARE nrows INT;
DECLARE rownum INT DEFAULT 1;
DECLARE ncols INT;
DECLARE colnum INT DEFAULT 1;
DECLARE ins_list TEXT DEFAULT '';
DECLARE val_list TEXT DEFAULT '';
DECLARE tmp VARCHAR(255);
-- load the XML file's contents into a string
SET xml = LOAD_FILE(file_name);
-- get the number of <row>s in this table
SET nrows = ExtractValue(xml,
'count(/mysqldump/database[@name=$database_name]/table_data[@name=$table_name]/row)');
-- get the number of <field>s (columns) in this table
SET ncols = ExtractValue(xml,
'count(/mysqldump/database[@name=$database_name]/table_data[@name=$table_name]/row[1]/field)');
-- for each <row>
WHILE rownum <= nrows DO
-- for each <field> (column)
WHILE colnum <= ncols DO
SET tmp = ExtractValue(xml,
'/mysqldump/database[@name=$database_name]/table_data[@name=$table_name]/row[$rownum]/field[$colnum]/@name');
SET ins_list = CONCAT(ins_list, tmp, IF(colnum<ncols, ',', ''));
SET tmp = ExtractValue(xml,
'/mysqldump/database[@name=$database_name]/table_data[@name=$table_name]/row[$rownum]/field[$colnum]');
SET val_list = CONCAT(val_list, '''', tmp ,'''', IF(colnum<ncols, ',', ''));
SET colnum = colnum + 1;
END WHILE;
SET @ins_text = CONCAT('INSERT INTO t1 (', ins_list, ') VALUES (', val_list, ')');
SET ins_list = '';
SET val_list = '';
PREPARE stmt FROM @ins_text;
EXECUTE stmt;
SET rownum = rownum + 1;
SET colnum = 1;
END WHILE;
END |
DELIMITER ;
Partager