MySQL: How to import XML File Data into a Table?

In this post, I am going to import XML file data into a table of MySQL Database.

MySQL provides LOAD XML INFILE to import XML file data into a table. We can use ROWS IDENTIFIED BY clause to read row by providing a XML node.
It supports three different type of XML formats.

1	<row column1="value1" column2="value2" .../>

1 2 3 4

<row>   <column1>value1</column1>   <column2>value2</column2> </row>

1 2 3 4

<row>   <field name='column1'>value1</field>   <field name='column2'>value2</field> </row>

Note: Table column name and XML column or tag name should be same otherwise it will not insert into a table.

Below is a small demonstration of this:

First, Create a sample XML file:
Name of this file is Employee.xml and I have saved in my local drive.

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15

<EmployeeDetails>   <employee EmpID="1" EmpFirstName="Anvesh" EmpLastName="Patel"/>   <employee EmpID="2" EmpFirstName="Neevan" EmpLastName="Patel"/>   <employee EmpID="3" EmpFirstName="Roy" EmpLastName="Jam"/>   <employee EmpID="4" EmpFirstName="Martin" EmpLastName="Loother"/>   <employee EmpID="5" EmpFirstName="Jenny" EmpLastName="Shah"/>   <employee EmpID="6" EmpFirstName="Kulin" EmpLastName="Patel"/>   <employee EmpID="7" EmpFirstName="Lobel" EmpLastName="Mary"/>   <employee EmpID="8" EmpFirstName="Muskan" EmpLastName="Khureshi"/>      <employee EmpID="9"> <EmpFirstName>Mahesh</EmpFirstName> <EmpLastName>Makad</EmpLastName> </employee>   <employee EmpID="10"> <EmpFirstName>Jemmy</EmpFirstName> <EmpLastName>Pandya</EmpLastName> </employee>   <employee EmpID="11"> <EmpFirstName>Mahi</EmpFirstName> <EmpLastName>Patel</EmpLastName> </employee>   <employee EmpID="12"> <EmpFirstName>Nivas</EmpFirstName> <EmpLastName>Rai</EmpLastName> </employee> </EmployeeDetails>

Create a sample table:

1 2 3 4 5 6

CREATE TABLE tbl_Employees (     EmpID INTEGER PRIMARY KEY     ,EmpFirstName VARCHAR(50)     ,EmpLastName VARCHAR(50) );

Import XML Data into a table:

1 2 3

LOAD XML LOCAL INFILE 'E:/Employee.xml' INTO TABLE tbl_Employees ROWS IDENTIFIED BY '<employee>';

Check the table:

1	SELECT *FROM tbl_Employees;