|
1 2 |
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 >).
To illustrate how this statement is used, suppose that we have a table created as follows:
|
1 2 3 4 5 6 7 8 |
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:
|
1 2 3 4 5 6 7 8 9 10 11 |
<?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 permissible 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:
|
1 |
LOAD XML LOCAL INFILE 'person.xml' INTO TABLE person ROWS IDENTIFIED BY '<person>'; |
mysql>
LOAD XML LOCAL INFILE 'person.xml'
->INTO TABLE person
->ROWS IDENTIFIED BY ''; Query OK, 6 rows affected (0.00 sec)
Records: 6 Deleted: 0 Skipped: 0 Warnings: 0Here, we assume that
person.xmlis 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)
More information