MySQL Forums
Forum List  »  Connector/Node.js

Can i use 'xmldump_load' in mysql 5.0.51?
Posted by: kanokwan cctek
Date: August 04, 2008 10:59PM

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 ;

Options: ReplyQuote


Subject
Written By
Posted
Can i use 'xmldump_load' in mysql 5.0.51?
August 04, 2008 10:59PM


Sorry, you can't reply to this topic. It has been closed.

Content reproduced on this site is the property of the respective copyright holders. It is not reviewed in advance by Oracle and does not necessarily represent the opinion of Oracle or any other party.