content_image/701092.jpg
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; 

<img src='content_image/701092.jpg'>

8 rows in set (0.00 sec) 

The order in which the fields are given within each row of the XML file does not affect the operation of LOAD XML ; the field order can vary from row to row, and is not required to be in the same order as the corresponding columns in the table. 

As mentioned previously, you can use a ( field_name_or_user_var , ...)  list of one or more XML fields (to select desired fields only) or user variables (to store the corresponding field values for later use). User variables can be especially useful when you want to insert data from an XML file into table columns whose names do not match those of the XML fields. To see how this works, we first create a table named individual  whose structure matches that of the person  table, but whose columns are named differently: 

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) 

In this case, you cannot simply load the XML file directly into the table, because the field and column names do not match: 

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 

This happens because the MySQL server looks for field names matching the column names of the target table. You can work around this problem by selecting the field values into user variables, then setting the 

