Why is the data not being loaded from the xml file?

Question

MySQL

There is a procedure for loading data from an xml file into a table using XPath functions:

DELIMITER $

CREATE PROCEDURE insert_xml(IN xml_file_name TEXT)
COMMENT "INSERTS INTO CLIENTS"
BEGIN
    DECLARE xml_file TEXT;
    DECLARE counter INT UNSIGNED DEFAULT 1;
    DECLARE size INT UNSIGNED DEFAULT ExtractValue(xml_file, "count(//row)");
    
    SET xml_file = LOAD_FILE(xml_file_name);
    
  IF xml_file IS NULL THEN
    SET @last_error = CONCAT("Couldn't load file ", xml_file_name);
    CALL non_existent();
  END IF;
    
  WHILE counter <= size DO
    INSERT INTO CLIENTS(id, num)
        VALUES
        	(
        	ExtractValue(xml_file, "//row[$counter]/id"), 
        	ExtractValue(xml_file, "//row[$counter]/num")
        	);
    SET counter = counter + 1;
  END WHILE;
END$

Here is the xml file itself:

<?xml version = "1.0" encoding = "utf-8"?>

<resultset>
  <row>
    <id>80</id>
    <num>81</num>
  </row>

  <row>
    <id>81</id>
    <num>81</num>
  </row>
</resultset>

Table definition:

CREATE TABLE `CLIENTS` (\n  `id` int(10) unsigned zerofill NOT NULL,\n  `num` bigint(20) NOT NULL,\n  PRIMARY KEY (`id`)\n) ENGINE=InnoDB DEFAULT CHARSET=latin1;

The procedure succeeds, but no new data is inserted into the table. With this LOAD XML LOCAL INFILE inserts the data successfully. Why is this happening?

Answer 1

generally based on this construction "//row[$counter]/id" there should be elements < row1 >, < row2 >, ...