MySQL - LOAD XML Statement

Table of content

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
Advertisements