mysql - 将 XML 导入 Mysql 参数存储过程

Question

我的以下存储过程有问题..我正在尝试从存储过程中读取 xml。

当我运行存储过程时，0 行受到影响..有人可以帮助我解决这个问题；)。我正在遵循下面的示例:

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>

行动:

20:51:01    call import_applicant_xml('C:\\applicants1.xml','/applicant_list/applicant')    0 row(s) affected   0.000 sec

Answer 1

我无法重现该问题。你能获取变量xml_content的内容吗？要获取变量 xml_content 的值，您可以执行以下操作:

.
.
.
CREATE PROCEDURE ...
.
.
.
  SET `xml_content` := LOAD_FILE(`path`);
  SELECT `xml_content`;
.
.
.
END//
.
.
.

我的测试:

文件:/path/to/file/applicants1.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>

MySQL 命令行:

mysql> DELIMITER //

mysql> DROP TABLE IF EXISTS `applicants`//
Query OK, 0 rows affected (0.00 sec)

mysql> DROP PROCEDURE IF EXISTS `import_applicant_xml`//
Query OK, 0 rows affected (0.00 sec)

mysql> CREATE TABLE IF NOT EXISTS `applicants` (   
    ->   `id`         INT          NOT NULL PRIMARY KEY,
    ->   `last_name`  VARCHAR(100) NOT NULL,
    ->   `first_name` VARCHAR(100) NULL
    -> )//
Query OK, 0 rows affected (0.00 sec)

mysql> CREATE 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//
Query OK, 0 rows affected (0.00 sec)

mysql> DELIMITER ;

mysql> CALL `import_applicant_xml`('/path/to/file/applicants1.xml',
    ->                             'applicant_list/applicant');
Query OK, 1 row affected (0.00 sec)

mysql> SELECT
    ->   `id`,
    ->   `last_name`,
    ->   `first_name`
    -> FROM
    ->   `applicants`;
+----+-----------+------------+
| id | last_name | first_name |
+----+-----------+------------+
|  1 | Rob       | Gravelle   |
|  2 | Al        | Bundy      |
|  3 | Little    | Richard    |
+----+-----------+------------+
3 rows in set (0.00 sec)