How to import XML file into MySQL database table using XML LOAD function

Question

I have an XML file which looks like this            lt  xml version  1 0  encoding  UTF-8   gt      lt resultset statement  YOUR SQL STATEMENTS TO GENERATE THIS XML FILE  xmlns xsi  http   www w3 org 2001 XMLSchema-instance  gt      lt row gt        lt field name  personal number  gt 539 lt  field gt        lt field name  firstname  gt Name lt  field gt        lt field name  lastname  gt Surname lt  field gt        lt field name  email  gt email domain com lt  field gt        lt field name  start time  gt 2011-04-02 13 30 00 lt  field gt        lt field name  end time  gt 2011-04-02 18 15 00 lt  field gt        lt field name  employee category  gt 1 2 4 5 22 37 38 39 41 43 44 lt  field gt      lt  row gt      lt row gt        lt field name  personal number  gt 539 lt  field gt        lt field name  firstname  gt Name lt  field gt        lt field name  lastname  gt Surname lt  field gt        lt field name  email  gt email domain com lt  field gt        lt field name  start time  gt 2011-04-02 13 30 00 lt  field gt        lt field name  end time  gt 2011-04-02 18 15 00 lt  field gt        lt field name  employee category  gt 1 2 4 5 22 37 38 39 41 43 44 lt  field gt      lt  row gt      lt row gt        lt field name  personal number  gt 539 lt  field gt        lt field name  firstname  gt Name lt  field gt        lt field name  lastname  gt Surname lt  field gt        lt field name  email  gt email domain com lt  field gt        lt field name  start time  gt 2011-04-02 13 30 00 lt  field gt        lt field name  end time  gt 2011-04-02 18 15 00 lt  field gt        lt field name  employee category  gt 1 2 4 5 22 37 38 39 41 43 44 lt  field gt      lt  row gt           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

User · Accepted Answer

you can specify fields like this   LOAD XML LOCAL INFILE   pathtofile file xml   INTO TABLE my tablename personal number  firstname

User · Answer

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

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

Examples related to mysql

Examples related to xml

Examples related to import

Examples related to mysql-loadfile