MySQL - LOAD XML Statement

---

---

LOAD XML Statement

Using the LOAD XML statement, you can insert the contents of an XML file into a MySQL table. If you use the LOCAL clause, you can upload the local files contents into a table.

Syntax

Following is the syntax of the LOAD XML Statement −

LOAD DATA
   [LOCAL]
      INFILE 'file_name'
      [REPLACE | IGNORE]
      INTO TABLE table_name
      [ROWS IDENTIFIED BY '<tagname>']
      [IGNORE number {LINES | ROWS}]
      [(field_name_or_user_var
         [, field_name_or_user_var] ...)]
      [SET col_name={expr | DEFAULT}
         [, col_name={expr | DEFAULT}] ...]

You can create an XML file in one of the following three ways −

<row column1="value1" column2="value2" .../>
Or,
<row>
   <column1>value1</column1>
   <column2>value2</column2>
</row>
Or,
<row>
   <field name='column1'>value1</field>
   <field name='column2'>value2</field>
</row>

Example

Assume we have created a table using the CREATE statement as shown below −

CREATE TABLE EMPLOYEE( MySQL LOAD XML Statement
   FIRST_NAME CHAR(20) NOT NULL,
   LAST_NAME CHAR(20),
   AGE INT,
   INCOME INT);

And if we have a file named data.xml with contents as −

<row>
   <FIRST_NAME>krishna</FIRST_NAME>
   <LAST_NAME>sharma</LAST_NAME>
   <AGE>19</AGE>
   <INCOME>2000</INCOME>
</row>
<row>
   <FIRST_NAME>raj</FIRST_NAME>
   <LAST_NAME>Kandukuri</LAST_NAME>
   <AGE>20</AGE>
   <INCOME>7000</INCOME>
</row>
<row>
   <FIRST_NAME>ramya</FIRST_NAME>
   <LAST_NAME>ramapriya</LAST_NAME>
   <AGE>25</AGE>
   <INCOME>5000</INCOME>
</row>
<row>
   <FIRST_NAME>Alexandra</FIRST_NAME>
   <LAST_NAME>Botez</LAST_NAME>
   <AGE>26</AGE>
   <INCOME>2000</INCOME>
</row>

Following query loads the contents of the data.xml file into the above created table −

load xml infile "C:/ProgramData/MySQL/MySQL Server 8.0/Uploads/data.xml" 
into table employee ROWS IDENTIFIED BY '<row>';

Verification

If you verify the contents of the Employee table you can observe the records in it as −

SELECT * FROM EMPLOYEE;

Output

The above mysql query generates the following output −

FIRST_NAME	LAST_NAME	AGE	INCOME
krishna	sharma	19	2000
raj	Kandukuri	20	7000
ramya	ramapriya	25	5000
Alexandra	Botez	26	2000

The SET clause

While working with the LOAD XML statement you need to make sure that the tag names in the xml file are same as the names of the columns of the table otherwise an error occurs.

In the LOAD XML statement, you can treat the values from the file as user variables, and assign them as values to the columns of a table using the SET clause.

Example

Assume we have created a table with name EMP using the CREATE statement as shown below −

CREATE TABLE EMP (FIRSTNAME VARCHAR(15), 
DEPARTMENT VARCHAR(25), SALARY INT);

Assume we have an xml file with name data.xml as shown below −

<row>
   <f_name>Rahman</f_name>
   <dpt>IT</age>
   <sal>5000</income>
</row>
<row>
   <f_name>Ram</f_name>
   <dpt>HR</age>
   <sal>7000</income>
</row>
<row>
   <f_name>Robert</f_name>
   <dpt>SALES</age>
   <sal>9000</income>
</row>
<row>
   <f_name>ramya</f_name>
   <dpt>IT</age>
   <sal>7000</income>
</row>

Following query reads the contents of the xml file in to the EMP table using the SET clause −

load xml infile "C:/ProgramData/MySQL/MySQL Server 
8.0/Uploads/data.xml"into table emp (@f_name, @dpt, @sal)
SET FIRSTNAME=@f_name, DEPARTMENT=@dpt, SALARY=@sal;

Verification

After executing the LOAD statement, you can verify the contents of the EMP table as shown below −

SELECT * FROM EMP;

Output

Following is the output of the above query −

FIRSTNAME	DEPARTMENT	SALARY
Rahman	IT	5000
Ram	HR	7000
Robert	SALES	9000
ramya	IT	7000

If you import the contents of an .xml file into a table using the LOAD XML statement, only the values corresponding to the columns in the person table, they are skipped.

Example

Assume we have created a table named TEST using the CREATE statement as shown below −

CREATE TABLE TEST (name VARCHAR(15), sal INT);

If we have an xml (data.xml) file as shown below −

<rowglt;
   <nameglt;Rahman</nameglt;
   <dptglt;IT</ageglt;
   <salglt;5000</incomeglt;
   <cityglt;Hyderabad</cityglt;
</rowglt;
<rowglt;
   <nameglt;Ram</nameglt;
   <dptglt;HR</ageglt;
   <salglt;7000</incomeglt;
   <cityglt;Vishakhapatnam</cityglt;
</rowglt;
<rowglt;
   <nameglt;Robert</nameglt;
   <dptglt;SALES</ageglt;
   <salglt;9000</incomeglt;
   <cityglt;Chennai</cityglt;
</rowglt;
<rowglt;
   <nameglt;ramya</nameglt;
   <dptglt;IT</ageglt;
   <salglt;7000</incomeglt;
   <cityglt;Delhi</cityglt;
</rowglt;

Following query loads the data from data.xml into the test table.

load xml infile "C:/ProgramData/MySQL/MySQL Server 
8.0/Uploads/data.xml" into table TEST ROWS IDENTIFIED BY '<row>';

Verification

After executing the LOAD statement, you can verify the contents of the test table as shown below −

SELECT * FROM TEST;

Output

The above query generates the following output −

name	sal
Rahman	5000
Ram	7000
Robert	9000
ramya	7000