Can i use 'xmldump_load' in mysql 5.0.51?
hello all,
I'm trying to put xml files into 'mysql version is '5.0.51b'from the code below
but the error is >>ERROR 1064 (42000): You have an error in your SQL syntax;check the manual that corresponds to your MySQL server version for the right syntax to use near ''/esp/bin/stmcode.xml','testdb','stmcode' at line 1<<
Please give me the suggestion.
DELIMITER |
DROP PROCEDURE IF EXISTS xmldump_load |
CREATE PROCEDURE xmldump_load ('/esp/bin/stmcode.xml','testdb','stmcode')
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('/esp/bin/stmcode.xml');
# get the number of <row>s in this table
SET nrows = ExtractValue(xml,
'count(/mysqldump/database[@name=$testdb]/table_data[@name=$stmcode]/row)');
# get the number of <field>s (columns) in this table
SET ncols = ExtractValue(xml,
'count(/mysqldump/database[@name=$testdb]/table_data[@name=$stmcode]/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=$testdb]/table_data[@name=$stmcode]/row[$rownum]/field[$colnum]/@name');
SET ins_list = CONCAT(ins_list, tmp, IF(colnum<ncols, ',', ''));
SET tmp = ExtractValue(xml,
'/mysqldump/database[@name=$testdb]/table_data[@name=$stmcode]/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 ;