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, you can invoke the mysql
client with the --xml and
-e options from
the system shell, as shown here:

shell> mysql --xml -e 'SELECT * FROM mydb.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:

<rowcolumn1="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:

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

(field_name_or_user_var,
...) is a list of one or more comma-separated XML fields
or user variables. The name of a user variable used for this
purpose must match the name of a field from the XML file, prefixed
with @. You can use field names to select only
desired fields. User variables can be employed to store the
corresponding field values for subsequent re-use.

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.

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, 8 rows were
imported into the test.person table. This can
be verified by a simple SELECT
statement:

There is no requirement that every field in the XML file be
matched with a column in the corresponding table. Fields which
have no corresponding columns are skipped. You can see this by
first emptying the person2 table and dropping
the created column, then using the same
LOAD XML statement we just employed previously,
like this:

The order in which the fields are given within each row of the XML
file does not affect the operation of LOAD XML;
the field order can vary from row to row, and is not required to
be in the same order as the corresponding columns in the table.

As mentioned previously, you can use a
(field_name_or_user_var,
...) list of one or more XML fields (to select desired
fields only) or user variables (to store the corresponding field
values for later use). User variables can be especially useful
when you want to insert data from an XML file into table columns
whose names do not match those of the XML fields. To see how this
works, we first create a table named individual
whose structure matches that of the person
table, but whose columns are named differently:

This happens because the MySQL server looks for field names
matching the column names of the target table. You can work around
this problem by selecting the field values into user variables,
then setting the target table's columns equal to the values
of those variables using SET. You can perform
both of these operations in a single statement, as shown here:

The names of the user variables must match
those of the corresponding fields from the XML file, with the
addition of the required @ prefix to indicate
that they are variables. The user variables need not be listed or
assigned in the same order as the corresponding fields.

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:

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.

For partitioned tables using storage engines that employ table
locks, such as MyISAM, any locks
caused by LOAD XML perform locks on all
partitions of the table. This does not apply to tables using
storage engines which employ row-level locking, such as
InnoDB. For more information, see
Section 19.6.4, “Partitioning and Locking”.

If your are using MySQL 5.1 or earlier, 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>

-- 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 importUPDATE 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%' ;