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
LINESIGNORE
句を指定すると、XML ファイル内の最初の number
ROWSnumber
行がスキップされます。これは、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 '<
句を使用すると、同じ XML ファイルのデータを定義の異なるデータベーステーブルにインポートできます。この例では、次の XML を含む tagname
>'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「パーティショニングとロック」を参照してください。