[mysql] How to import XML file into MySQL database table using XML_LOAD(); function

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>

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

use databasename;
LOAD XML LOCAL INFILE '/pathtofile/file.xml' INTO TABLE 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)

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

I am using MySQL 5.1.56

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?

Thank you!

This question is related to mysql xml import mysql-loadfile

The answer is


you can specify fields like this:

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

Since ID is auto increment, you can also specify ID=NULL as,

LOAD XML LOCAL INFILE '/pathtofile/file.xml' INTO TABLE my_tablename SET ID=NULL;


Examples related to mysql

Implement specialization in ER diagram How to post query parameters with Axios? PHP with MySQL 8.0+ error: The server requested authentication method unknown to the client Loading class `com.mysql.jdbc.Driver'. This is deprecated. The new driver class is `com.mysql.cj.jdbc.Driver' phpMyAdmin - Error > Incorrect format parameter? Authentication plugin 'caching_sha2_password' is not supported How to resolve Unable to load authentication plugin 'caching_sha2_password' issue Connection Java-MySql : Public Key Retrieval is not allowed How to grant all privileges to root user in MySQL 8.0 MySQL 8.0 - Client does not support authentication protocol requested by server; consider upgrading MySQL client

Examples related to xml

strange error in my Animation Drawable How do I POST XML data to a webservice with Postman? PHP XML Extension: Not installed How to add a Hint in spinner in XML Generating Request/Response XML from a WSDL Manifest Merger failed with multiple errors in Android Studio How to set menu to Toolbar in Android How to add colored border on cardview? Android: ScrollView vs NestedScrollView WARNING: Exception encountered during context initialization - cancelling refresh attempt

Examples related to import

Import functions from another js file. Javascript The difference between "require(x)" and "import x" pytest cannot import module while python can How to import an Excel file into SQL Server? When should I use curly braces for ES6 import? How to import a JSON file in ECMAScript 6? Python: Importing urllib.quote importing external ".txt" file in python beyond top level package error in relative import Reading tab-delimited file with Pandas - works on Windows, but not on Mac

Examples related to mysql-loadfile

Using PHP to upload file and add the path to MySQL database How to insert image in mysql database(table)? MYSQL import data from csv using LOAD DATA INFILE MySQL: Enable LOAD DATA LOCAL INFILE How to import XML file into MySQL database table using XML_LOAD(); function How to insert selected columns from a CSV file to a MySQL database using LOAD DATA INFILE