MySQL :: MySQL 5.6 リファレンスマニュアル

8.0 English
5.7 English
5.6 English
5.5 English

MySQL 5.6 リファレンスマニュアル / ... / LOAD XML 構文

13.2.7 LOAD XML 構文

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

LOAD XML ステートメントは、XML ファイルからテーブルにデータを読み取ります。file_name は、リテラル文字列として指定する必要があります。オプションの ROWS IDENTIFIED BY 句内の tagname もリテラル文字列として指定し、山括弧 (< および >) で囲む必要があります。

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

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

そのファイルをテーブルに読み戻すには、LOAD XML INFILE を使用します。デフォルトでは、<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 つのすべての形式を使用できます。インポートルーチンは各行の形式を自動的に検出し、それを正しく解釈します。タグは、タグまたは属性名とカラム名に基づいて照合されます。

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

LOW_PRIORITY または CONCURRENT
LOCAL
REPLACE または IGNORE
PARTITION
CHARACTER SET
(column_or_user_var,...)
SET

これらの句の詳細は、セクション13.2.6「LOAD DATA INFILE 構文」を参照してください。

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

このステートメントがどのように使用されるかを示すために、次のように作成されたテーブルがあるとします。

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 があるとします。

<?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>

例として示したこのファイルには、前に説明した許可される各 XML 形式が表されています。

person.xml 内のデータを person テーブルにインポートするには、次のステートメントを使用できます。

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

ここでは、person.xml が MySQL データディレクトリ内に存在することを前提にしています。このファイルが見つからない場合は、次のエラーが発生します。

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

ROWS IDENTIFIED BY '<person>' 句は、XML ファイル内の各 <person> 要素が、このデータがインポートされるテーブル内の各行と同等であると見なされることを示します。この場合、これは test データベース内の person テーブルです。

サーバーからの応答でわかるように、test.person テーブルには 6 行がインポートされました。これは、単純な SELECT ステートメントで確認できます。

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)

このセクションの前の方で説明したように、許可される 3 つの XML 形式のいずれかまたはすべてを 1 つのファイルに含め、それを LOAD XML を使用して読み取ることができます。

上の操作の逆、つまり、MySQL テーブルデータの XML ファイルへのダンプは、次に示すように、システムシェルから mysql クライアントを使用して実現できます。

注記

--xml オプションを指定すると、mysql クライアントは、その出力として XML 形式を使用します。-e オプションを指定すると、クライアントはそのオプションの直後にある SQL ステートメントを実行します。

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>

次のように、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, 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)

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=latin1
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 INFILE ステートメント (および LOAD_FILE() 関数) を実行できなくなるだけです。LOAD XML LOCAL INFILE の実行は妨げられません。このステートメントを禁止するには、サーバーまたはクライアントを --local-infile=OFF で起動する必要があります。

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

テーブルロックを採用したストレージエンジン (MyISAM など) を使用しているパーティション化されたテーブルの場合、LOAD XML はどのパーティションロックもプルーニングできません。これは、行レベルロックを採用したストレージエンジン (InnoDB など) を使用しているテーブルには適用されません。詳細は、セクション19.6.4「パーティショニングとロック」を参照してください。

PREV HOME UP NEXT

User Comments

Posted by Harald Groven on January 3, 2013

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>

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