如何使用XML_LOAD（）将XML文件导入MySQL数据库表;功能 [英] How to import XML file into MySQL database table using XML_LOAD(); function

问题描述

我有一个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>

我正在尝试导入它在MySQL中使用SQL语句：

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文件中不存在该字段ID。如何使用内置函数的MySQL查询导入此XML文件，以便在导入期间跳过id列并依赖于id列的自动增量函数？
是否有一些更聪明的方法来处理MySQL文件导入？
也许有更好的声明允许指定列映射？

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?

谢谢！

推荐答案

你可以指定这样的字段：

you can specify fields like this:

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

这篇关于如何使用XML_LOAD（）将XML文件导入MySQL数据库表;功能的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！