如何使用 XML_LOAD() 将 XML 文件导入 MySQL 数据库表；功能

2021-11-20 00:00:00 xml import mysql

我有一个如下所示的 XML 文件:

I have an XML file which looks like this : 

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

<resultset statement="YOUR SQL STATEMENTS TO GENERATE THIS XML FILE" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance">
  <row>
    <field name="personal_number">539</field>
    <field name="firstname">Name</field>
    <field name="lastname">Surname</field>
    <field name="email">email.domain.com</field>
    <field name="start_time">2011-04-02 13:30:00</field>
    <field name="end_time">2011-04-02 18:15:00</field>
    <field name="employee_category">1,2,4,5,22,37,38,39,41,43,44</field>
  </row>
  <row>
    <field name="personal_number">539</field>
    <field name="firstname">Name</field>
    <field name="lastname">Surname</field>
    <field name="email">email.domain.com</field>
    <field name="start_time">2011-04-02 13:30:00</field>
    <field name="end_time">2011-04-02 18:15:00</field>
    <field name="employee_category">1,2,4,5,22,37,38,39,41,43,44</field>
  </row>
  <row>
    <field name="personal_number">539</field>
    <field name="firstname">Name</field>
    <field name="lastname">Surname</field>
    <field name="email">email.domain.com</field>
    <field name="start_time">2011-04-02 13:30:00</field>
    <field name="end_time">2011-04-02 18:15:00</field>
    <field name="employee_category">1,2,4,5,22,37,38,39,41,43,44</field>
  </row>

我正在尝试使用 SQL 语句将其导入 MySQL:

I am trying to import it in MySQL using SQL statement : 

use databasename;
LOAD XML LOCAL INFILE '/pathtofile/file.xml' INTO TABLE my_tablename;

表 my_tablename 具有以下字段:

The table my_tablename has the following fields : 

id (auto increment id)
personal_number(varchar)
firstname(varchar) 
lastname(varchar)
email(varchar) 
start_time(varchar)
end_time(varchar)
employee_category(varchar)

我收到错误:错误代码:1136列数与第 1 行的值数不匹配

I get error : Error Code: 1136 Column count doesn't match value count at row 1

我使用的是 MySQL 5.1.56

I am using MySQL 5.1.56

我认为发生此错误是因为数据库表具有字段 id，该字段在 XML 文件中不存在.如何使用内置函数的 MySQL 查询导入此 XML 文件，以便在导入过程中跳过 id 列并依赖于 id 列的自动增量功能?是否有一些更智能的方法来处理 MySQL 中的 XML 文件导入?也许有更好的语句允许指定列映射?

I assume this error occurs because the database table has field id, which is not present in the XML file. How is it possible to import this XML file using MySQL queries of built in functions such that it skips id column during the import and relies on the auto increment function for the id column? Is there some smarter way of handling XML file imports im MySQL? Maybe there is better statement which allows to specify column mapping?

谢谢！

推荐答案

您可以指定如下字段:

LOAD XML LOCAL INFILE '/pathtofile/file.xml' 
INTO TABLE my_tablename(personal_number, firstname, ...);

相关文章