XML : Import XML to Mysql Parameter Stored Procedure

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:

http://www.databasejournal.com/features/mysql/importing-xml-data-into-mysql-tables-using-a-stored-procedure.html

  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    

Path Xml file

No comments:

Post a Comment