MySQL :: MySQL 5.5 Reference Manual :: 12.2.7 LOAD XML Syntax

MySQL 5.5 Reference Manual :: 12 SQL Statement Syntax :: 12.2 Data Manipulation Statements :: 12.2.7 LOAD XML Syntax

Section Navigation [Toggle]

12.2.7. `LOAD XML` Syntax

LOAD XML [LOW_PRIORITY | CONCURRENT] [LOCAL] INFILE 'file_name'
[REPLACE | IGNORE]
INTO TABLE [db_name.]tbl_name
[CHARACTER SET charset_name]
[ROWS IDENTIFIED BY '<tagname>']
[IGNORE number [LINES | ROWS]]
[(column_or_user_var,...)]
[SET col_name = expr,...]

The LOAD XML statement reads data from an XML file into a table. The file_name must be given as a literal string. The tagname in the optional ROWS IDENTIFIED BY clause must also be given as a literal string, and must be surrounded by angle brackets (< and >).

LOAD XML acts as the complement of running the mysql client in XML output mode (that is, starting the client with the --xml option). To write data from a table to an XML file, use a command such as the following one from the system shell:

shell> mysql --xml -e 'SELECT * FROM mytable' > file.xml

To read the file back into a table, use LOAD XML INFILE. By default, the <row> element is considered to be the equivalent of a database table row; this can be changed using the ROWS IDENTIFIED BY clause.

This statement supports three different XML formats:

Column names as attributes and column values as attribute values:
```
<row column1="value1" column2="value2" .../>
```
Column names as tags and column values as the content of these tags:
```
<row>
  <column1>value1</column1>
  <column2>value2</column2>
</row>
```
Column names are the name attributes of <field> tags, and values are the contents of these tags:
```
<row>
  <field name='column1'>value1</field>
  <field name='column2'>value2</field>
</row>
```
This is the format used by other MySQL tools, such as mysqldump.

All 3 formats can be used in the same XML file; the import routine automatically detects the format for each row and interpets it correctly. Tags are matched based on the tag or attribute name and the column name.

The following clauses work essentially the same way for LOAD XML as they do for LOAD DATA:

LOW_PRIORITY or CONCURRENT
LOCAL
REPLACE or IGNORE
CHARACTER SET
(column_or_user_var,...)
SET

See Section 12.2.6, “LOAD DATA INFILE Syntax”, for more information about these clauses.

The IGNORE number LINES or IGNORE number ROWS clause causes the first number rows in the XML file to be skipped. It is analogous to the LOAD DATA statement's IGNORE ... LINES clause.

To illustrate how this statement is used, suppose that we have a table created as follows:

USE test;

CREATE TABLE person (
    person_id INT NOT NULL PRIMARY KEY,
    fname VARCHAR(40) NULL,
    lname VARCHAR(40) NULL,
    created TIMESTAMP
);

Suppose further that this table is initially empty.

Now suppose that we have a simple XML file person.xml, whose contents are as shown here:

<?xml version="1.0"?>
<list>
  <person person_id="1" fname="Pekka" lname="Nousiainen"/>
  <person person_id="2" fname="Jonas" lname="Oreland"/>
  <person person_id="3"><fname>Mikael</fname><lname>Ronström</lname></person>
  <person person_id="4"><fname>Lars</fname><lname>Thalmann</lname></person>
  <person><field name="person_id">5</field><field name="fname">Tomas</field><field name="lname">Ulin</field></person>
  <person><field name="person_id">6</field><field name="fname">Martin</field><field name="lname">Sköld</field></person>
</list>

Each of the allowable XML formats discussed previously is represented in this example file.

To import the data in person.xml into the person table, you can use this statement:

mysql> LOAD XML LOCAL INFILE 'person.xml'
    ->   INTO TABLE person
    ->   ROWS IDENTIFIED BY '<person>';

Query OK, 6 rows affected (0.00 sec)
Records: 6  Deleted: 0  Skipped: 0  Warnings: 0

Here, we assume that person.xml is located in the MySQL data directory. If the file cannot be found, the following error results:

ERROR 2 (HY000): File '/person.xml' not found (Errcode: 2)

The ROWS IDENTIFIED BY '<person>' clause means that each <person> element in the XML file is considered equivalent to a row in the table into which the data is to be imported. In this case, this is the person table in the test database.

As can be seen by the response from the server, 6 rows were imported into the test.person table. This can be verified by a simple SELECT statement:

mysql> SELECT * FROM person;
+-----------+--------+------------+---------------------+
| person_id | fname  | lname      | created             |
+-----------+--------+------------+---------------------+
|         1 | Pekka  | Nousiainen | 2007-07-13 16:18:47 |
|         2 | Jonas  | Oreland    | 2007-07-13 16:18:47 |
|         3 | Mikael | Ronström   | 2007-07-13 16:18:47 |
|         4 | Lars   | Thalmann   | 2007-07-13 16:18:47 |
|         5 | Tomas  | Ulin       | 2007-07-13 16:18:47 |
|         6 | Martin | Sköld      | 2007-07-13 16:18:47 |
+-----------+--------+------------+---------------------+
6 rows in set (0.00 sec)

This shows, as stated earlier in this section, that any or all of the 3 permitted XML formats may appear in a single file and be read in using LOAD XML.

The inverse of the above operation — that is, dumping MySQL table data into an XML file — can be accomplished using the mysql client from the system shell, as shown here:

Note

The --xml option causes the mysql client to use XML formatting for its output; the -e option causes the client to execute the SQL statement immediately following the option.

shell> mysql --xml -e "SELECT * FROM test.person" > person-dump.xml
shell> cat person-dump.xml
<?xml version="1.0"?>

<resultset statement="SELECT * FROM test.person" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance">
  <row>
        <field name="person_id">1</field>
        <field name="fname">Pekka</field>
        <field name="lname">Nousiainen</field>
        <field name="created">2007-07-13 16:18:47</field>
  </row>

  <row>
        <field name="person_id">2</field>
        <field name="fname">Jonas</field>
        <field name="lname">Oreland</field>
        <field name="created">2007-07-13 16:18:47</field>
  </row>

  <row>
        <field name="person_id">3</field>
        <field name="fname">Mikael</field>
        <field name="lname">Ronström</field>
        <field name="created">2007-07-13 16:18:47</field>
  </row>

  <row>
        <field name="person_id">4</field>
        <field name="fname">Lars</field>
        <field name="lname">Thalmann</field>
        <field name="created">2007-07-13 16:18:47</field>
  </row>

  <row>
        <field name="person_id">5</field>
        <field name="fname">Tomas</field>
        <field name="lname">Ulin</field>
        <field name="created">2007-07-13 16:18:47</field>
  </row>

  <row>
        <field name="person_id">6</field>
        <field name="fname">Martin</field>
        <field name="lname">Sköld</field>
        <field name="created">2007-07-13 16:18:47</field>
  </row>
</resultset>

You can verify that the dump is valid by creating a copy of the person and then importing the dump file into the new table, like this:

mysql> USE test;
mysql> CREATE TABLE person2 LIKE person;
Query OK, 0 rows affected (0.00 sec)

mysql> LOAD XML LOCAL INFILE 'person-dump.xml'
    ->   INTO TABLE person2;
Query OK, 6 rows affected (0.01 sec)
Records: 6  Deleted: 0  Skipped: 0  Warnings: 0

mysql> SELECT * FROM person2;
+-----------+--------+------------+---------------------+
| person_id | fname  | lname      | created             |
+-----------+--------+------------+---------------------+
|         1 | Pekka  | Nousiainen | 2007-07-13 16:18:47 |
|         2 | Jonas  | Oreland    | 2007-07-13 16:18:47 |
|         3 | Mikael | Ronström   | 2007-07-13 16:18:47 |
|         4 | Lars   | Thalmann   | 2007-07-13 16:18:47 |
|         5 | Tomas  | Ulin       | 2007-07-13 16:18:47 |
|         6 | Martin | Sköld      | 2007-07-13 16:18:47 |
+-----------+--------+------------+---------------------+
6 rows in set (0.00 sec)

Using a ROWS IDENTIFIED BY '<tagname>' clause, it is possible to import data from the same XML file into database tables with different definitions. For this example, suppose that you have a file named address.xml which contains the following XML:

<?xml version="1.0"?>

<list>
  <person person_id="1">
    <fname>Robert</fname>
    <lname>Jones</lname>
    <address address_id="1" street="Mill Creek Road" zip="45365" city="Sidney"/>
    <address address_id="2" street="Main Street" zip="28681" city="Taylorsville"/>
  </person>

  <person person_id="2">
    <fname>Mary</fname>
    <lname>Smith</lname>
    <address address_id="3" street="River Road" zip="80239" city="Denver"/>
    <!-- <address address_id="4" street="North Street" zip="37920" city="Knoxville"/> -->
  </person>

</list>

You can again use the test.person table as defined previously in this section, after clearing all the existing records from the table and then showing its structure as shown here:

mysql< TRUNCATE person;
Query OK, 0 rows affected (0.04 sec)

mysql< SHOW CREATE TABLE person\G
*************************** 1. row ***************************
       Table: person
Create Table: CREATE TABLE `person` (
  `person_id` int(11) NOT NULL,
  `fname` varchar(40) DEFAULT NULL,
  `lname` varchar(40) DEFAULT NULL,
  `created` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  PRIMARY KEY (`person_id`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1
1 row in set (0.00 sec)

Now create an address table in the test database using the following CREATE TABLE statement:

CREATE TABLE address (
    address_id INT NOT NULL PRIMARY KEY,
    person_id INT NULL,
    street VARCHAR(40) NULL,
    zip INT NULL,
    city VARCHAR(40) NULL,
    created TIMESTAMP
);

To import the data from the XML file into the person table, execute the following LOAD XML statement, which specifies that rows are to be specified by the <person> element, as shown here;

mysql> LOAD XML LOCAL INFILE 'address.xml'
    ->   INTO TABLE person
    ->   ROWS IDENTIFIED BY '<person>';
Query OK, 2 rows affected (0.00 sec)
Records: 2  Deleted: 0  Skipped: 0  Warnings: 0

You can verify that the records were imported using a SELECT statement:

mysql> SELECT * FROM person;
+-----------+--------+-------+---------------------+
| person_id | fname  | lname | created             |
+-----------+--------+-------+---------------------+
|         1 | Robert | Jones | 2007-07-24 17:37:06 |
|         2 | Mary   | Smith | 2007-07-24 17:37:06 |
+-----------+--------+-------+---------------------+
2 rows in set (0.00 sec)

Since the <address> elements in the XML file have no corresponding columns in the person table, they are skipped.

To import the data from the <address> elements into the address table, use the LOAD XML statement shown here:

mysql> LOAD XML LOCAL INFILE 'address.xml'
    ->   INTO TABLE address
    ->   ROWS IDENTIFIED BY '<address>';
Query OK, 3 rows affected (0.00 sec)
Records: 3  Deleted: 0  Skipped: 0  Warnings: 0

You can see that the data was imported using a SELECT statement such as this one:

mysql> SELECT * FROM address;
+------------+-----------+-----------------+-------+--------------+---------------------+
| address_id | person_id | street          | zip   | city         | created             |
+------------+-----------+-----------------+-------+--------------+---------------------+
|          1 |         1 | Mill Creek Road | 45365 | Sidney       | 2007-07-24 17:37:37 |
|          2 |         1 | Main Street     | 28681 | Taylorsville | 2007-07-24 17:37:37 |
|          3 |         2 | River Road      | 80239 | Denver       | 2007-07-24 17:37:37 |
+------------+-----------+-----------------+-------+--------------+---------------------+
3 rows in set (0.00 sec)

The data from the <address> element that is enclosed in XML comments is not imported. However, since there is a person_id column in the address table, the value of the person_id attribute from the parent <person> element for each <address> is imported into the address table.

Security Considerations. As with the LOAD DATA statement, the transfer of the XML file from the client host to the server host is initiated by the MySQL server. In theory, a patched server could be built that would tell the client program to transfer a file of the server's choosing rather than the file named by the client in the LOAD XML statement. Such a server could access any file on the client host to which the client user has read access.

In a Web environment, clients usually connect to MySQL from a Web server. A user that can run any command against the MySQL server can use LOAD XML LOCAL to read any files to which the Web server process has read access. In this environment, the client with respect to the MySQL server is actually the Web server, not the remote program being run by the user who connects to the Web server.

You can disable loading of XML files from clients by starting the server with --local-infile=0 or --local-infile=OFF. This option can also be used when starting the mysql client to disable LOAD XML for the duration of the client session.

To prevent a client from loading XML files from the server, do not grant the FILE privilege to the corresponding MySQL user account, or revoke this privilege if the client user account already has it.

Important

Revoking the FILE privilege (or not granting it in the first place) keeps the user only from executing the LOAD XML INFILE statement (as well as the LOAD_FILE() function; it does not prevent the user from executing LOAD XML LOCAL INFILE. To disallow this statement, you must start the server or the client with --local-infile=OFF.

In other words, the FILE privilege affects only whether the client can read files on the server; it has no bearing on whether the client can read files on the local file system.

Previous / Next / Up / Table of Contents

User Comments

Posted by Harald Groven on October 16 2008 5:14pm

[Delete] [Edit]

If your are using an earlier version of Mysql than 6.0+ or 5.2, you can fake the LOAD XML functions by using using a few steps involving LOAD DATA INFILE, CONCAT and SUBSTRING_INDEX. In this example, we assume that we want to create one record for each occurrence of a tag <program> .... </program>

-- 1st create a table to hold the XML-data
CREATE TABLE IF NOT EXISTS myxmltable (
xmldata longtext NOT NULL
) DEFAULT CHARSET=utf8 ;

-- split each record for each occurrence of an end tag,
-- in this case it is "</program>"
LOAD DATA INFILE '/home/myusername/file.xml'
INTO TABLE `myxmltable`
CHARACTER SET latin1
-- FIELDS TERMINATED BY ''
LINES TERMINATED BY '</program>' ;

-- reintroduce the end tag that was lost during import
UPDATE myxmltable
SET xmldata=CONCAT(xmldata, '\n</program>')
WHERE xmldata LIKE '%<program%' ;

-- remove all xml-in the first record before first occurrence of start tag <program>
UPDATE myxmltable
SET xmldata=CONCAT('<program', SUBSTRING_INDEX(xmldata,'<program',-1))
WHERE xmldata LIKE '%<program%' ;

Obviously... LOAD XML saves a lot of unnecessary coding :-)

Add your own comment.

Top / Previous / Next / Up / Table of Contents

12.2.7. LOAD XML Syntax

Note

Important

User Comments

12.2.7. `LOAD XML` Syntax