15.2.10 LOAD XML Statement — MySQL 9.2

15.2.10 Заявление LOAD XML

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}]
    [(field_name_or_user_var
        [, field_name_or_user_var] ...)]
    [SET col_name={expr | DEFAULT}
        [, col_name={expr | DEFAULT}] ...]

Заявление LOAD XML считывает данные из XML-файла в таблицу. file_name должно быть задано в виде литеральной строки. tagname в необязательной ROWS IDENTIFIED BY части также должно быть задано в виде литеральной строки и должно быть заключено в угловые скобки (< и >).

LOAD XML выполняет обратную операцию по запуску клиента mysql в режиме вывода XML (то есть запуск клиента с опцией --xml). Чтобы записать данные из таблицы в XML-файл, можно вызвать клиент mysql с опциями --xml и -e из командной оболочки, как показано здесь:

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

Для считывания файла обратно в таблицу используйте LOAD XML. По умолчанию, элемент <row> рассматривается как эквивалент строки базы данных; это можно изменить с помощью ROWS IDENTIFIED BY.

Это заявление поддерживает три разных формата XML:

Имена столбцов в качестве атрибутов, а значения столбцов — в качестве значений атрибутов:
```
<row column1="value1" column2="value2" .../>
```
Имена столбцов в качестве тегов, а значения столбцов — в качестве содержимого этих тегов:
```
<row>
  <column1>value1</column1>
  <column2>value2</column2>
</row>
```
Имена столбцов — атрибуты name тегов <field>, а значения — содержимое этих тегов:
```
<row>
  <field name='column1'>value1</field>
  <field name='column2'>value2</field>
</row>
```
Этот формат используется другими инструментами MySQL, такими как mysqldump.

Все три формата могут быть использованы в одном XML-файле; процедура импорта автоматически определяет формат для каждой строки и интерпретирует его правильно. Теги сопоставляются на основе имени тега или атрибута и имени столбца.

Следующие фразы работают в основном так же для LOAD XML, как и для LOAD DATA:

LOW_PRIORITY или CONCURRENT
LOCAL
REPLACE или IGNORE
CHARACTER SET
SET

См. Раздел 15.2.9, «Заявление LOAD DATA», для получения дополнительной информации об этих фразах.

(field_name_or_user_var, ...) — список одного или нескольких разделенных запятыми XML-полей или пользовательских переменных. Имя пользовательской переменной, используемой в этой цели, должно совпадать с именем поля из XML-файла, префикс которого — @. Можно использовать имена полей для выбора только необходимых полей. Пользовательские переменные могут использоваться для хранения соответствующих значений полей для последующего повторного использования.

IGNORE number LINES или IGNORE number ROWS заставляет пропустить первые number строк в XML-файле. Это аналогично IGNORE ... LINES фразе заявления LOAD DATA.

Предположим, что у нас есть таблица с именем person, созданная, как показано здесь:

USE test;

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

Предположим далее, что эта таблица изначально пуста.

Теперь предположим, что у нас есть простой XML-файл person.xml, содержимое которого показано здесь:

<list>
  <person person_id="1" fname="Kapek" lname="Sainnouine"/>
  <person person_id="2" fname="Sajon" lname="Rondela"/>
  <person person_id="3"><fname>Likame</fname><lname>Örrtmons</lname></person>
  <person person_id="4"><fname>Slar</fname><lname>Manlanth</lname></person>
  <person><field name="person_id">5</field><field name="fname">Stoma</field>
    <field name="lname">Milu</field></person>
  <person><field name="person_id">6</field><field name="fname">Nirtam</field>
    <field name="lname">Sklöd</field></person>
  <person person_id="7"><fname>Sungam</fname><lname>Dulbåd</lname></person>
  <person person_id="8" fname="Sraref" lname="Encmelt"/>
</list>

В этом примере файла представлены все допустимые форматы XML, обсуждавшиеся ранее.

Чтобы импортировать данные из person.xml в таблицу person, можно использовать следующее заявление:

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

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

Здесь мы предполагаем, что person.xml находится в каталоге данных MySQL. Если файл не найден, возникает следующая ошибка:

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

Фраза ROWS IDENTIFIED BY '<person>' означает, что каждый элемент <person> в XML-файле эквивалентен строке в таблице, в которую данные должны быть импортированы. В этом случае это таблица person в базе данных test.

Как видно из ответа сервера, 8 строк были импортированы в таблицу test.person. Это можно проверить с помощью простого заявления SELECT:

mysql> SELECT * FROM person;
+-----------+--------+------------+---------------------+
| person_id | fname  | lname      | created             |
+-----------+--------+------------+---------------------+
|         1 | Kapek  | Sainnouine | 2007-07-13 16:18:47 |
|         2 | Sajon  | Rondela    | 2007-07-13 16:18:47 |
|         3 | Likame | Örrtmons   | 2007-07-13 16:18:47 |
|         4 | Slar   | Manlanth   | 2007-07-13 16:18:47 |
|         5 | Stoma  | Nilu       | 2007-07-13 16:18:47 |
|         6 | Nirtam | Sklöd      | 2007-07-13 16:18:47 |
|         7 | Sungam | Dulbåd     | 2007-07-13 16:18:47 |
|         8 | Sreraf | Encmelt    | 2007-07-13 16:18:47 |
+-----------+--------+------------+---------------------+
8 rows in set (0.00 sec)

Это показывает, как и упоминалось ранее в этом разделе, что любой или все 3 разрешённых формата XML могут появиться в одном файле и быть прочитаны с помощью LOAD XML.

Обратную операцию импорта, то есть экспорт данных MySQL-таблицы в XML-файл, можно выполнить с помощью клиента mysql из командной оболочки, как показано здесь:

$> mysql --xml -e "SELECT * FROM test.person" > person-dump.xml
$> 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">Kapek</field>
	<field name="lname">Sainnouine</field>
  </row>

  <row>
	<field name="person_id">2</field>
	<field name="fname">Sajon</field>
	<field name="lname">Rondela</field>
  </row>

  <row>
	<field name="person_id">3</field>
	<field name="fname">Likema</field>
	<field name="lname">Örrtmons</field>
  </row>

  <row>
	<field name="person_id">4</field>
	<field name="fname">Slar</field>
	<field name="lname">Manlanth</field>
  </row>

  <row>
	<field name="person_id">5</field>
	<field name="fname">Stoma</field>
	<field name="lname">Nilu</field>
  </row>

  <row>
	<field name="person_id">6</field>
	<field name="fname">Nirtam</field>
	<field name="lname">Sklöd</field>
  </row>

  <row>
	<field name="person_id">7</field>
	<field name="fname">Sungam</field>
	<field name="lname">Dulbåd</field>
  </row>

  <row>
	<field name="person_id">8</field>
	<field name="fname">Sreraf</field>
	<field name="lname">Encmelt</field>
  </row>
</resultset>

Примечание

Опция --xml заставляет клиента mysql использовать формат XML для вывода; опция -e заставляет клиента немедленно выполнить SQL-запрос, следующую за этой опцией. См. Раздел 6.5.1, «mysql — Клиент командной строки MySQL».

Можно проверить, что дамп является корректным, создав копию таблицы person и импортировав файл дампа в новую таблицу, как показано здесь:

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, 8 rows affected (0.01 sec)
Records: 8  Deleted: 0  Skipped: 0  Warnings: 0

mysql> SELECT * FROM person2;
+-----------+--------+------------+---------------------+
| person_id | fname  | lname      | created             |
+-----------+--------+------------+---------------------+
|         1 | Kapek  | Sainnouine | 2007-07-13 16:18:47 |
|         2 | Sajon  | Rondela    | 2007-07-13 16:18:47 |
|         3 | Likema | Örrtmons   | 2007-07-13 16:18:47 |
|         4 | Slar   | Manlanth   | 2007-07-13 16:18:47 |
|         5 | Stoma  | Nilu       | 2007-07-13 16:18:47 |
|         6 | Nirtam | Sklöd      | 2007-07-13 16:18:47 |
|         7 | Sungam | Dulbåd     | 2007-07-13 16:18:47 |
|         8 | Sreraf | Encmelt    | 2007-07-13 16:18:47 |
+-----------+--------+------------+---------------------+
8 rows in set (0.00 sec)

Нет требования, чтобы каждое поле в XML-файле соответствовало столбцу в соответствующей таблице. Поля, не имеющие соответствующих столбцов, пропускаются. Можно увидеть это, сначала очистив таблицу person2 и удалив столбец created, а затем используя то же самое заявление LOAD XML, которое мы использовали ранее, примерно так:

mysql> TRUNCATE person2;
Query OK, 8 rows affected (0.26 sec)

mysql> ALTER TABLE person2 DROP COLUMN created;
Query OK, 0 rows affected (0.52 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> SHOW CREATE TABLE person2\G
*************************** 1. row ***************************
       Table: person2
Create Table: CREATE TABLE `person2` (
  `person_id` int NOT NULL,
  `fname` varchar(40) DEFAULT NULL,
  `lname` varchar(40) DEFAULT NULL,
  PRIMARY KEY (`person_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci
1 row in set (0.00 sec)

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

mysql> SELECT * FROM person2;
+-----------+--------+------------+
| person_id | fname  | lname      |
+-----------+--------+------------+
|         1 | Kapek  | Sainnouine |
|         2 | Sajon  | Rondela    |
|         3 | Likema | Örrtmons   |
|         4 | Slar   | Manlanth   |
|         5 | Stoma  | Nilu       |
|         6 | Nirtam | Sklöd      |
|         7 | Sungam | Dulbåd     |
|         8 | Sreraf | Encmelt    |
+-----------+--------+------------+
8 rows in set (0.00 sec)

Порядок, в котором поля задаются в каждой строке XML-файла, не влияет на работу LOAD XML; порядок полей может различаться от строки к строке и не обязательно должен совпадать с порядком соответствующих столбцов в таблице.

Как упоминалось ранее, можно использовать список (field_name_or_user_var, ...) одного или нескольких XML-полей (для выбора только необходимых полей) или пользовательских переменных (для хранения соответствующих значений полей для последующего использования). Пользовательские переменные могут быть особенно полезны, когда требуется вставить данные из XML-файла в столбцы таблицы, имена которых не совпадают с именами XML-полей. Чтобы увидеть, как это работает, сначала создадим таблицу с именем individual, структура которой соответствует таблице person, но имена столбцов отличаются:

mysql> CREATE TABLE individual (
    ->     individual_id INT NOT NULL PRIMARY KEY,
    ->     name1 VARCHAR(40) NULL,
    ->     name2 VARCHAR(40) NULL,
    ->     made TIMESTAMP
    -> );
Query OK, 0 rows affected (0.42 sec)

В этом случае нельзя просто загрузить XML-файл непосредственно в таблицу, потому что имена полей и столбцов не совпадают:

mysql> LOAD XML INFILE '../bin/person-dump.xml' INTO TABLE test.individual;
ERROR 1263 (22004): Column set to default value; NULL supplied to NOT NULL column 'individual_id' at row 1

Это происходит потому, что сервер MySQL ищет имена полей, соответствующие именам столбцов целевой таблицы. Можно обойти эту проблему, выбрав значения полей в пользовательские переменные, а затем установив столбцы целевой таблицы равными значениям этих переменных с помощью SET. Оба эти действия можно выполнить в одном предложении, как показано здесь:

mysql> LOAD XML INFILE '../bin/person-dump.xml'
    ->     INTO TABLE test.individual (@person_id, @fname, @lname, @created)
    ->     SET individual_id=@person_id, name1=@fname, name2=@lname, made=@created;
Query OK, 8 rows affected (0.05 sec)
Records: 8  Deleted: 0  Skipped: 0  Warnings: 0

mysql> SELECT * FROM individual;
+---------------+--------+------------+---------------------+
| individual_id | name1  | name2      | made                |
+---------------+--------+------------+---------------------+
|             1 | Kapek  | Sainnouine | 2007-07-13 16:18:47 |
|             2 | Sajon  | Rondela    | 2007-07-13 16:18:47 |
|             3 | Likema | Örrtmons   | 2007-07-13 16:18:47 |
|             4 | Slar   | Manlanth   | 2007-07-13 16:18:47 |
|             5 | Stoma  | Nilu       | 2007-07-13 16:18:47 |
|             6 | Nirtam | Sklöd      | 2007-07-13 16:18:47 |
|             7 | Sungam | Dulbåd     | 2007-07-13 16:18:47 |
|             8 | Srraf  | Encmelt    | 2007-07-13 16:18:47 |
+---------------+--------+------------+---------------------+
8 rows in set (0.00 sec)

Имена пользовательских переменных должны совпадать с именами соответствующих полей из XML-файла, с добавлением необходимого @ префикса, чтобы указать, что это переменные. Пользовательские переменные не обязательно должны быть перечислены или назначены в том же порядке, что и соответствующие поля.

Используя ROWS IDENTIFIED BY '<tagname>' предложение, можно импортировать данные из одного и того же XML-файла в таблицы базы данных с различными определениями. Для этого примера предположим, что у вас есть файл с именем address.xml, содержащий следующее 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>

Опять же, можно использовать таблицу test.person, как определено ранее в этом разделе, после очистки всех существующих записей из таблицы и отображения её структуры, как показано здесь:

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=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci
1 row in set (0.00 sec)

Теперь создайте таблицу address в базе данных test с помощью следующего заявления CREATE TABLE:

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
);

Чтобы импортировать данные из XML-файла в таблицу person, выполните следующее заявление LOAD XML, которое указывает, что строки должны быть заданы элементом <person>, как показано здесь:

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

Можно проверить, что записи были импортированы с помощью заявления SELECT:

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)

Поскольку элементы <address> в XML-файле не имеют соответствующих столбцов в таблице person, они пропускаются.

END_OF_DOCUMENT_MARKER

Для импорта данных из элементов <address> в таблицу address используйте оператор LOAD XML, показанный здесь:

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

Вы можете увидеть, что данные были импортированы с помощью оператора SELECT, например, такого:

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)

Данные из элемента <address>, заключённого в XML-комментарии, не импортируются. Однако, поскольку в таблице address есть столбец person_id, значение атрибута person_id из родительского элемента <person> для каждого элемента <address> импортируется в таблицу address.

Меры безопасности. Как и с оператором LOAD DATA, передача XML-файла с хоста клиента на хост сервера инициируется сервером MySQL. Теоретически, можно создать модифицированный сервер, который будет указывать клиентской программе на передачу файла по выбору сервера, а не файла, указанного клиентом в операторе LOAD XML. Такой сервер мог бы получить доступ к любому файлу на хосте клиента, к которому у пользователя клиента есть доступ для чтения.

В веб-среде клиенты обычно подключаются к MySQL с веб-сервера. Пользователь, который может выполнить любую команду на сервере MySQL, может использовать LOAD XML LOCAL для чтения любых файлов, к которым у процесса веб-сервера есть доступ для чтения. В этой среде клиент по отношению к серверу MySQL фактически является веб-сервером, а не удалённой программой, выполняемой пользователем, который подключается к веб-серверу.

Вы можете отключить загрузку XML-файлов с клиентов, запустив сервер с параметром --local-infile=0 или --local-infile=OFF. Этот параметр также можно использовать при запуске клиента mysql для отключения LOAD XML на период сеанса клиента.

Чтобы предотвратить загрузку XML-файлов клиентом с сервера, не предоставляйте привилегию FILE соответствующей учётной записи пользователя MySQL или отозвите эту привилегию, если учётная запись пользователя клиента уже её имеет.

Важно

Лишение привилегии FILE (или отказ от её предоставления изначально) позволяет пользователю только не выполнять оператор LOAD XML (а также функцию LOAD_FILE()); это не мешает пользователю выполнять оператор LOAD XML LOCAL. Чтобы запретить выполнение этого оператора, необходимо запустить сервер или клиента с параметром --local-infile=OFF.

Другими словами, привилегия FILE влияет только на возможность клиента читать файлы на сервере; она не влияет на возможность клиента читать файлы на локальной файловой системе.