MySQL :: MySQL 8.0 リファレンスマニュアル :: 13.2.8 LOAD XML ステートメント

MySQL 8.0 リファレンスマニュアル / ... / LOAD XML ステートメント

13.2.8 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 は、リテラル文字列として指定する必要があります。オプションの ROWS IDENTIFIED BY 句内の tagname もリテラル文字列として指定し、山括弧 (< および >) で囲む必要があります。

LOAD XML は、XML 出力モードでの mysql クライアントの実行 (つまり、--xml オプションを使用したクライアントの起動) を補完するものとして機能します。テーブルから XML ファイルにデータを書き込むには、次に示すように、システムシェルから --xml および -e オプションを指定して mysql クライアントを呼び出すことができます:

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

ファイルをテーブルに読み取るには、LOAD XML を使用します。デフォルトでは、<row> 要素は、データベーステーブル行と同等であると見なされます。これは、ROWS IDENTIFIED BY 句を使用して変更できます。

このステートメントは、次の 3 つの異なる XML 形式をサポートします。

属性としてのカラム名と、属性値としてのカラム値:
```
<row column1="value1" column2="value2" .../>
```
タグとしてのカラム名と、これらのタグの内容としてのカラム値:
```
<row>
  <column1>value1</column1>
  <column2>value2</column2>
</row>
```
カラム名は <field> タグの name 属性で、値はこれらのタグの内容:
```
<row>
  <field name='column1'>value1</field>
  <field name='column2'>value2</field>
</row>
```
これは、mysqldump などのほかの MySQL ツールによって使用される形式です。

同じ XML ファイルで 3 つのすべての形式を使用できます。インポートルーチンは各行の形式を自動的に検出し、それを正しく解釈します。タグは、タグまたは属性名とカラム名に基づいて照合されます。

MySQL 8.0.21 より前は、LOAD XML はソース XML の CDATA セクションをサポートしていませんでした。 (Bug #30753708、Bug #98199)

次の句は、基本的に LOAD XML に対して LOAD DATA に対する場合と同じように機能します。

LOW_PRIORITY または CONCURRENT
LOCAL
REPLACE または IGNORE
CHARACTER SET
SET

これらの句の詳細は、セクション13.2.7「LOAD DATA ステートメント」を参照してください。

(field_name_or_user_var, ...) は、カンマ区切りの XML フィールドまたはユーザー変数のリストです。この目的で使用されるユーザー変数の名前は、接頭辞@が付いた XML ファイルのフィールドの名前と一致する必要があります。フィールド名を使用して、目的のフィールドのみを選択できます。ユーザー変数を使用して、後続の再利用のために対応するフィールド値を格納できます。

IGNORE number LINES または IGNORE number ROWS 句を指定すると、XML ファイル内の最初の number 行がスキップされます。これは、LOAD DATA ステートメントの IGNORE ... LINES 句に類似しています。

次に示すように作成された 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>' 句は、XML ファイル内の各 <person> 要素が、このデータがインポートされるテーブル内の各行と同等であると見なされることを示します。この場合、これは test データベース内の person テーブルです。

サーバーからの応答でわかるように、test.person テーブルには 8 行がインポートされました。これは、単純な 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 クライアントをシステムシェルから使用します:

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">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 ステートメントを実行します。セクション4.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(11) NOT NULL,
  `fname` varchar(40) DEFAULT NULL,
  `lname` varchar(40) DEFAULT NULL,
  PRIMARY KEY (`person_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8
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 の操作には影響しません。フィールドの順序は行によって異なる場合があり、テーブル内の対応するカラムと同じ順序である必要はありません。

前述のように、XML の 1 つ以上のフィールド (目的のフィールドのみを選択する場合) またはユーザー変数 (後で使用するために対応するフィールド値を格納する場合) の (field_name_or_user_var, ...) リストを使用できます。ユーザー変数は、XML ファイルの名前が XML フィールドの名前と一致しないテーブルのカラムにデータを挿入する場合に特に役立ちます。これがどのように機能するかを確認するには、まず、person テーブルの構造と一致するが、カラムの名前が異なる individual という名前のテーブルを作成します:

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 ファイルのデータを定義の異なるデータベーステーブルにインポートできます。この例では、次の XML を含む address.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=MyISAM DEFAULT CHARSET=utf8mb4
1 row in set (0.00 sec)

次に、次の CREATE TABLE ステートメントを使用して、test データベース内に address テーブルを作成します。

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 テーブルにインポートするには、次に示すように、行が <person> 要素で指定されるように指定する次の LOAD XML ステートメントを実行します。

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)

XML ファイル内の <address> 要素は、person テーブル内に対応するカラムがないためスキップされます。

<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 カラムがあるため、各 <address> に対する親の <person> 要素の person_id 属性の値は address テーブルにインポートされます。

セキュリティー上の考慮事項. LOAD DATA ステートメントと同様に、クライアントホストからサーバーホストへの XML ファイルの転送は MySQL サーバーによって開始されます。理論上は、LOAD XML ステートメント内でクライアントによって指定されたファイルではなく、サーバーが選択したファイルを転送するようにクライアントプログラムに指示する、パッチが適用されたサーバーを構築できます。そのようなサーバーは、クライアントユーザーが読み取りアクセス権を持つクライアントホスト上のすべてのファイルにアクセスできます。

Web 環境では、クライアントは通常、Web サーバーから MySQL に接続します。 MySQL サーバーに対して任意のコマンドを実行できるユーザーは、LOAD XML LOCAL を使用して、Web サーバープロセスが読み取りアクセス権を持つどのファイルでも読み取ることができます。この環境では、そのクライアントは MySQL サーバーに対して、Web サーバーに接続するユーザーによって実行されているリモートプログラムではなく、実際に Web サーバーです。

--local-infile=0 または --local-infile=OFF を使用してサーバーを起動することによって、クライアントからの XML ファイルのロードを無効にすることができます。このオプションはまた、クライアントセッションの期間中は LOAD XML を無効にするように mysql クライアントを起動する場合にも使用できます。

クライアントがサーバーから XML ファイルをロードしないようにするために、対応する MySQL ユーザーアカウントには FILE 権限を付与しないようにするか、またはクライアントユーザーアカウントがすでにこの権限を持っている場合は取り消してください。

重要

FILE 権限を取り消す (または最初に付与しない) と、ユーザーは LOAD XML ステートメント (および LOAD_FILE() 関数) の実行のみを保持し、LOAD XML LOCAL の実行を妨げることはありません。このステートメントを禁止するには、サーバーまたはクライアントを --local-infile=OFF で起動する必要があります。

つまり、FILE 権限は、そのクライアントがサーバー上のファイルを読み取れるかどうかにのみ影響を与えます。そのクライアントがローカルファイルシステム上のファイルを読み取れるかどうかには関係しません。

関連キーワード: 　ステートメント, person, TABLE, CREATE, テーブル, fname, lname, LOAD, row, サーバー

PREV HOME UP NEXT