15.2.10 LOAD XML Statement

15.2.10 LOAD XML Statement

1LOAD XML
2    [LOW_PRIORITY | CONCURRENT] [LOCAL]
3    INFILE 'file_name'
4    [REPLACE | IGNORE]
5    INTO TABLE [db_name.]tbl_name
6    [CHARACTER SET charset_name]
7    [ROWS IDENTIFIED BY '<tagname>']
8    [IGNORE number {LINES | ROWS}]
9    [(field_name_or_user_var        [, field_name_or_user_var] ...)]
10    [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 클라이언트를 다음과 같이 호출할 수 있습니다:

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

파일을 다시 테이블로 읽어들이려면 LOAD XML을 사용합니다. 기본적으로 <row> 엘리먼트가 데이터베이스 테이블 행과 동등한 것으로 간주됩니다. 이는 ROWS IDENTIFIED BY 절을 사용하여 변경할 수 있습니다.

이 문은 세 가지 서로 다른 XML 형식을 지원합니다:

• 컬럼 이름이 애트리뷰트이고 컬럼 값이 애트리뷰트 값인 경우:

1<row column1="value1" column2="value2" .../>

• 컬럼 이름이 태그이고 컬럼 값이 이 태그들의 콘텐츠인 경우:

1<row>
2    <column1>value1</column1>
3    <column2>value2</column2>
4</row>

• 컬럼 이름이 <field> 태그의 name 애트리뷰트이고, 값이 이 태그들의 콘텐츠인 경우:

1<row>
2    <field name='column1'>value1</field>
3    <field name='column2'>value2</field>
4</row>

이 형식은 mysqldump와 같은 다른 MySQL 도구에서 사용됩니다.

세 형식 모두 동일한 XML 파일에서 사용할 수 있습니다. 임포트 루틴은 각 행의 형식을 자동으로 감지하고 올바르게 해석합니다. 태그는 태그 또는 애트리뷰트 이름과 컬럼 이름을 기준으로 매칭됩니다.

다음 절들은 LOAD XML에서 LOAD DATA에서와 사실상 동일한 방식으로 동작합니다:

• LOW_PRIORITY 또는 CONCURRENT

• LOCAL

• REPLACE 또는 IGNORE

• CHARACTER SET

• SET

이들 절에 대한 자세한 내용은 Section 15.2.9, “LOAD DATA Statement”를 참조하십시오.

(field_name_or_user_var, ...)는 하나 이상의 콤마로 구분된 XML 필드 또는 사용자 변수 목록입니다. 이 용도로 사용되는 사용자 변수의 이름은 XML 파일의 필드 이름과 일치해야 하며, 앞에 @가 붙어야 합니다. 필드 이름을 사용하여 원하는 필드만 선택할 수 있습니다. 사용자 변수는 이후 재사용을 위해 해당 필드 값을 저장하는 데 사용할 수 있습니다.

IGNORE number LINES 또는 IGNORE number ROWS 절은 XML 파일의 처음 number 개 행을 건너뛰게 합니다. 이는 LOAD DATA 문의 IGNORE ... LINES 절과 유사합니다.

다음과 같이 생성된 person이라는 이름의 테이블이 있다고 가정해 봅니다:

1USE test;
2
3CREATE TABLE person (
4    person_id INT NOT NULL PRIMARY KEY,
5    fname VARCHAR(40) NULL,
6    lname VARCHAR(40) NULL,
7    created TIMESTAMP
8);

또한 이 테이블이 처음에는 비어 있다고 가정합니다.

이제 person.xml이라는 간단한 XML 파일이 있고, 그 내용이 다음과 같다고 가정합니다:

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

이 예제 파일에는 앞에서 설명한 허용 가능한 XML 형식 각각이 모두 나타나 있습니다.

person.xml의 데이터를 person 테이블로 임포트하려면 다음 문을 사용할 수 있습니다:

1mysql> LOAD XML LOCAL INFILE 'person.xml'
2    ->   INTO TABLE person
3    ->   ROWS IDENTIFIED BY '<person>';
4
5Query OK, 8 rows affected (0.00 sec)
6Records: 8  Deleted: 0  Skipped: 0  Warnings: 0

여기서는 person.xml이 MySQL 데이터 디렉터리에 있다고 가정합니다. 파일을 찾을 수 없는 경우 다음과 같은 오류가 발생합니다:

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

ROWS IDENTIFIED BY '<person>' 절은 XML 파일의 각 <person> 엘리먼트가 데이터를 임포트할 대상 테이블의 행과 동등한 것으로 간주된다는 것을 의미합니다. 이 경우, 대상은 test 데이터베이스의 person 테이블입니다.

서버의 응답에서 볼 수 있듯이, 8개의 행이 test.person 테이블로 임포트되었습니다. 이는 간단한 SELECT 문으로 확인할 수 있습니다:

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

이는, 이 섹션의 앞부분에서 언급한 대로, 허용된 3가지 XML 형식 가운데 하나 또는 모두가 하나의 파일에 나타나더라도, LOAD XML을 사용해 읽어들일 수 있음을 보여 줍니다.

방금 본 임포트 작업의 반대—즉, MySQL 테이블 데이터를 XML 파일로 덤프하는 작업은, 시스템 셸에서 mysql 클라이언트를 사용하여 다음과 같이 수행할 수 있습니다:

1$> mysql --xml -e "SELECT * FROM test.person" > person-dump.xml
2$> cat person-dump.xml
3<?xml version="1.0"?>
4
5<resultset statement="SELECT * FROM test.person" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance">
6  <row>
7	<field name="person_id">1</field>
8	<field name="fname">Kapek</field>
9	<field name="lname">Sainnouine</field>
10  </row>
11
12  <row>
13	<field name="person_id">2</field>
14	<field name="fname">Sajon</field>
15	<field name="lname">Rondela</field>
16  </row>
17
18  <row>
19	<field name="person_id">3</field>
20	<field name="fname">Likema</field>
21	<field name="lname">Örrtmons</field>
22  </row>
23
24  <row>
25	<field name="person_id">4</field>
26	<field name="fname">Slar</field>
27	<field name="lname">Manlanth</field>
28  </row>
29
30  <row>
31	<field name="person_id">5</field>
32	<field name="fname">Stoma</field>
33	<field name="lname">Nilu</field>
34  </row>
35
36  <row>
37	<field name="person_id">6</field>
38	<field name="fname">Nirtam</field>
39	<field name="lname">Sklöd</field>
40  </row>
41
42  <row>
43	<field name="person_id">7</field>
44	<field name="fname">Sungam</field>
45	<field name="lname">Dulbåd</field>
46  </row>
47
48  <row>
49	<field name="person_id">8</field>
50	<field name="fname">Sreraf</field>
51	<field name="lname">Encmelt</field>
52  </row>
53</resultset>

참고

--xml 옵션은 mysql 클라이언트가 출력에 XML 포맷을 사용하도록 합니다. -e 옵션은 클라이언트가 옵션 바로 뒤에 오는 SQL 문을 실행하도록 합니다. Section 6.5.1, “mysql — The MySQL Command-Line Client”를 참조하십시오.

person 테이블의 복사본을 만들고 덤프 파일을 새 테이블로 임포트하여 덤프가 유효한지 확인할 수 있습니다. 다음과 같이 합니다:

1mysql> USE test;
2mysql> CREATE TABLE person2 LIKE person;
3Query OK, 0 rows affected (0.00 sec)
4
5mysql> LOAD XML LOCAL INFILE 'person-dump.xml'
6    ->   INTO TABLE person2;
7Query OK, 8 rows affected (0.01 sec)
8Records: 8  Deleted: 0  Skipped: 0  Warnings: 0
9
10mysql> SELECT * FROM person2;
11+-----------+--------+------------+---------------------+
12| person_id | fname  | lname      | created             |
13+-----------+--------+------------+---------------------+
14|         1 | Kapek  | Sainnouine | 2007-07-13 16:18:47 |
15|         2 | Sajon  | Rondela    | 2007-07-13 16:18:47 |
16|         3 | Likema | Örrtmons   | 2007-07-13 16:18:47 |
17|         4 | Slar   | Manlanth   | 2007-07-13 16:18:47 |
18|         5 | Stoma  | Nilu       | 2007-07-13 16:18:47 |
19|         6 | Nirtam | Sklöd      | 2007-07-13 16:18:47 |
20|         7 | Sungam | Dulbåd     | 2007-07-13 16:18:47 |
21|         8 | Sreraf | Encmelt    | 2007-07-13 16:18:47 |
22+-----------+--------+------------+---------------------+
238 rows in set (0.00 sec)

XML 파일의 모든 필드가 해당 테이블의 컬럼과 매칭될 필요는 없습니다. 대응하는 컬럼이 없는 필드는 건너뛰어집니다. 이는 먼저 person2 테이블을 비우고 created 컬럼을 드롭한 다음, 바로 앞에서 사용했던 것과 동일한 LOAD XML 문을 다시 사용함으로써 확인할 수 있습니다. 다음과 같습니다:

1mysql> TRUNCATE person2;
2Query OK, 8 rows affected (0.26 sec)
3
4mysql> ALTER TABLE person2 DROP COLUMN created;
5Query OK, 0 rows affected (0.52 sec)
6Records: 0  Duplicates: 0  Warnings: 0
7
8mysql> SHOW CREATE TABLE person2\G
9*************************** 1. row ***************************
10       Table: person2
11Create Table: CREATE TABLE `person2` (
12  `person_id` int NOT NULL,
13  `fname` varchar(40) DEFAULT NULL,
14  `lname` varchar(40) DEFAULT NULL,
15  PRIMARY KEY (`person_id`)
16) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci
171 row in set (0.00 sec)
18
19mysql> LOAD XML LOCAL INFILE 'person-dump.xml'
20    ->   INTO TABLE person2;
21Query OK, 8 rows affected (0.01 sec)
22Records: 8  Deleted: 0  Skipped: 0  Warnings: 0
23
24mysql> SELECT * FROM person2;
25+-----------+--------+------------+
26| person_id | fname  | lname      |
27+-----------+--------+------------+
28|         1 | Kapek  | Sainnouine |
29|         2 | Sajon  | Rondela    |
30|         3 | Likema | Örrtmons   |
31|         4 | Slar   | Manlanth   |
32|         5 | Stoma  | Nilu       |
33|         6 | Nirtam | Sklöd      |
34|         7 | Sungam | Dulbåd     |
35|         8 | Sreraf | Encmelt    |
36+-----------+--------+------------+
378 rows in set (0.00 sec)

XML 파일의 각 행 안에서 필드가 주어지는 순서는 LOAD XML의 동작에 영향을 주지 않습니다. 필드 순서는 행마다 달라질 수 있으며, 테이블의 해당 컬럼 순서와 동일할 필요가 없습니다.

앞에서 언급했듯이, (field_name_or_user_var, ...) 목록에서 하나 이상의 XML 필드(원하는 필드만 선택하기 위해) 또는 사용자 변수(나중에 사용할 수 있도록 해당 필드 값을 저장하기 위해)를 사용할 수 있습니다. 사용자 변수는 XML 파일의 데이터를, 이름이 XML 필드와 일치하지 않는 테이블 컬럼에 삽입하고자 할 때 특히 유용할 수 있습니다. 이 동작 방식을 살펴보기 위해, 먼저 person 테이블과 구조는 동일하지만 컬럼 이름이 다른 individual이라는 이름의 테이블을 생성합니다:

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

이 경우에는 필드와 컬럼 이름이 일치하지 않으므로 XML 파일을 단순히 테이블에 직접 로드할 수 없습니다:

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

이는 MySQL 서버가 대상 테이블의 컬럼 이름과 일치하는 필드 이름을 찾기 때문입니다. 이 문제는 필드 값을 사용자 변수에 선택한 다음, SET을 사용하여 대상 테이블의 컬럼을 해당 변수 값과 같도록 설정함으로써 우회할 수 있습니다. 이 두 작업은 다음과 같이 하나의 문에서 모두 수행할 수 있습니다:

1mysql> LOAD XML INFILE '../bin/person-dump.xml'
2    ->     INTO TABLE test.individual (@person_id, @fname, @lname, @created)
3    ->     SET individual_id=@person_id, name1=@fname, name2=@lname, made=@created;
4Query OK, 8 rows affected (0.05 sec)
5Records: 8  Deleted: 0  Skipped: 0  Warnings: 0
6
7mysql> SELECT * FROM individual;
8+---------------+--------+------------+---------------------+
9| individual_id | name1  | name2      | made                |
10+---------------+--------+------------+---------------------+
11|             1 | Kapek  | Sainnouine | 2007-07-13 16:18:47 |
12|             2 | Sajon  | Rondela    | 2007-07-13 16:18:47 |
13|             3 | Likema | Örrtmons   | 2007-07-13 16:18:47 |
14|             4 | Slar   | Manlanth   | 2007-07-13 16:18:47 |
15|             5 | Stoma  | Nilu       | 2007-07-13 16:18:47 |
16|             6 | Nirtam | Sklöd      | 2007-07-13 16:18:47 |
17|             7 | Sungam | Dulbåd     | 2007-07-13 16:18:47 |
18|             8 | Srraf  | Encmelt    | 2007-07-13 16:18:47 |
19+---------------+--------+------------+---------------------+
208 rows in set (0.00 sec)

사용자 변수의 이름은 반드시 XML 파일의 해당 필드 이름과 일치해야 하며, 변수임을 나타내는 필수 @ 프리픽스가 추가되어야 합니다. 사용자 변수는 해당 필드와 동일한 순서로 나열되거나 할당될 필요는 없습니다.

ROWS IDENTIFIED BY '<tagname>' 절을 사용하면 동일한 XML 파일에서 서로 다른 정의를 가진 데이터베이스 테이블로 데이터를 임포트할 수 있습니다. 이 예제를 위해, 다음 XML을 포함하는 address.xml이라는 파일이 있다고 가정합니다:

1<?xml version="1.0"?>
2
3<list>
4  <person person_id="1">
5    <fname>Robert</fname>
6    <lname>Jones</lname>
7    <address address_id="1" street="Mill Creek Road" zip="45365" city="Sidney"/>
8    <address address_id="2" street="Main Street" zip="28681" city="Taylorsville"/>
9  </person>
10
11  <person person_id="2">
12    <fname>Mary</fname>
13    <lname>Smith</lname>
14    <address address_id="3" street="River Road" zip="80239" city="Denver"/>
15    <!-- <address address_id="4" street="North Street" zip="37920" city="Knoxville"/> -->
16  </person>
17
18</list>

이 섹션의 앞에서 정의한 대로 test.person 테이블을 다시 사용할 수 있습니다. 먼저 테이블의 기존 레코드를 모두 지우고, 이어서 그 구조를 다음과 같이 표시합니다:

1mysql< TRUNCATE person;
2Query OK, 0 rows affected (0.04 sec)
3
4mysql< SHOW CREATE TABLE person\G
5*************************** 1. row ***************************
6       Table: person
7Create Table: CREATE TABLE `person` (
8  `person_id` int(11) NOT NULL,
9  `fname` varchar(40) DEFAULT NULL,
10  `lname` varchar(40) DEFAULT NULL,
11  `created` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
12  PRIMARY KEY (`person_id`)
13) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci
141 row in set (0.00 sec)

이제 다음 CREATE TABLE 문을 사용하여 test 데이터베이스에 address 테이블을 생성합니다:

1CREATE TABLE address (
2    address_id INT NOT NULL PRIMARY KEY,
3    person_id INT NULL,
4    street VARCHAR(40) NULL,
5    zip INT NULL,
6    city VARCHAR(40) NULL,
7    created TIMESTAMP
8);

XML 파일의 데이터를 person 테이블로 임포트하기 위해, 다음과 같이 행이 <person> 엘리먼트에 의해 지정되도록 하는 LOAD XML 문을 실행합니다:

1mysql> LOAD XML LOCAL INFILE 'address.xml'
2    ->   INTO TABLE person
3    ->   ROWS IDENTIFIED BY '<person>';
4Query OK, 2 rows affected (0.00 sec)
5Records: 2  Deleted: 0  Skipped: 0  Warnings: 0

SELECT 문을 사용하여 레코드가 임포트되었는지 확인할 수 있습니다:

1mysql> SELECT * FROM person;
2+-----------+--------+-------+---------------------+
3| person_id | fname  | lname | created             |
4+-----------+--------+-------+---------------------+
5|         1 | Robert | Jones | 2007-07-24 17:37:06 |
6|         2 | Mary   | Smith | 2007-07-24 17:37:06 |
7+-----------+--------+-------+---------------------+
82 rows in set (0.00 sec)

XML 파일의 <address> 엘리먼트에는 person 테이블에 대응하는 컬럼이 없으므로, 이들은 건너뛰어집니다.

<address> 엘리먼트의 데이터를 address 테이블로 임포트하려면, 다음에 나오는 LOAD XML 문을 사용합니다:

1mysql> LOAD XML LOCAL INFILE 'address.xml'
2    ->   INTO TABLE address
3    ->   ROWS IDENTIFIED BY '<address>';
4Query OK, 3 rows affected (0.00 sec)
5Records: 3  Deleted: 0  Skipped: 0  Warnings: 0

다음과 같은 SELECT 문을 사용하여 데이터가 임포트되었는지 확인할 수 있습니다:

1mysql> SELECT * FROM address;
2+------------+-----------+-----------------+-------+--------------+---------------------+
3| address_id | person_id | street          | zip   | city         | created             |
4+------------+-----------+-----------------+-------+--------------+---------------------+
5|          1 |         1 | Mill Creek Road | 45365 | Sidney       | 2007-07-24 17:37:37 |
6|          2 |         1 | Main Street     | 28681 | Taylorsville | 2007-07-24 17:37:37 |
7|          3 |         2 | River Road      | 80239 | Denver       | 2007-07-24 17:37:37 |
8+------------+-----------+-----------------+-------+--------------+---------------------+
93 rows in set (0.00 sec)

XML 주석으로 둘러싸인 <address> 엘리먼트의 데이터는 임포트되지 않습니다. 그러나 address 테이블에는 person_id 컬럼이 있으므로, 각 <address>에 대한 상위 <person> 엘리먼트의 person_id 애트리뷰트 값은 address 테이블로 임포트됩니다.

Security Considerations.

LOAD DATA 문과 마찬가지로, 클라이언트 호스트에서 서버 호스트로의 XML 파일 전송은 MySQL 서버에 의해 시작됩니다. 이론적으로, 클라이언트가 LOAD XML 문에서 지정한 파일 대신, 서버가 선택한 파일을 전송하도록 클라이언트 프로그램에 지시하는 패치된 서버를 만들 수 있습니다. 이러한 서버는 클라이언트 사용자에게 읽기 권한이 있는 클라이언트 호스트의 모든 파일에 접근할 수 있습니다.

Web 환경에서, 클라이언트는 보통 Web 서버에서 MySQL에 연결합니다. MySQL 서버에 대해 임의의 명령을 실행할 수 있는 사용자는 LOAD XML LOCAL을 사용해서 Web 서버 프로세스가 읽기 권한을 가진 모든 파일을 읽을 수 있습니다. 이 환경에서는, MySQL 서버 입장에서의 클라이언트는 실제로 Web 서버이며, Web 서버에 연결하는 사용자가 실행 중인 원격 프로그램이 아닙니다.

클라이언트로부터 XML 파일을 로드하는 기능을 비활성화하려면, 서버를 --local-infile=0 또는 --local-infile=OFF와 함께 시작합니다. 이 옵션은 mysql 클라이언트를 시작할 때도 사용할 수 있으며, 클라이언트 세션 동안 LOAD XML을 비활성화합니다.

클라이언트가 서버에서 XML 파일을 로드하지 못하게 하려면, 해당 MySQL 사용자 계정에 FILE 권한을 부여하지 않거나, 이미 권한이 부여된 경우 이를 리voke하십시오.

주의

FILE 권한을 리voke(또는 처음부터 그랜트하지 않음)하면 사용자가 LOAD XML 문(및 LOAD_FILE() 함수)을 실행하지 못하게 할 뿐이며, LOAD XML LOCAL 문의 실행까지 막지는 못합니다. 이 문을 허용하지 않으려면, 서버 또는 클라이언트를 --local-infile=OFF와 함께 시작해야 합니다.

즉, FILE 권한은 클라이언트가 서버에서 파일을 읽을 수 있는지만 좌우할 뿐이며, 클라이언트가 로컬 파일 시스템에서 파일을 읽을 수 있는지 여부에는 영향을 미치지 않습니다.