I have a problem with the following stored procedure.. I'm trying to read a xml from a stored procedure.
When I run the stored procedure I get 0 row ( s ) affected .. Someone can help me with this problem ;). I was following the example below:
CREATE DEFINER=`root`@`localhost` PROCEDURE `import_applicant_xml`(path varchar(255), node varchar(255)) BEGIN declare xml_content text; declare v_row_index int unsigned default 0; declare v_row_count int unsigned; declare v_xpath_row varchar(255); set xml_content = load_file(path); -- calculate the number of row elements. set v_row_count = extractValue(xml_content, concat('count(', node, ')')); -- loop through all the row elements WHILE v_row_index < v_row_count DO set v_row_index = v_row_index + 1; set v_xpath_row = concat(node, '[', v_row_index, ']/@*'); insert into applicants values ( extractValue(xml_content, concat(v_xpath_row, '[1]')), extractValue(xml_content, concat(v_xpath_row, '[2]')), extractValue(xml_content, concat(v_xpath_row, '[3]')) ); END WHILE; END $$ DELIMITER ; call import_applicant_xml('C:\\applicants1.xml','/applicant_list/applicant'); XML:
<?xml version="1.0"?> <applicant_list> <applicant id="1" fname="Rob" lname="Gravelle"/> <applicant id="2" fname="Al" lname="Bundy"/> <applicant id="3" fname="Little" lname="Richard"/> </applicant_list> Action:
20:51:01 call import_applicant_xml('C:\\applicants1.xml','/applicant_list/applicant') 0 row(s) affected 0.000 sec
No comments:
Post a Comment