The fields in the XML file that don't correspond to physical column names are ignored. And columns in the table that don't have corresponding fields in the XML are set NULL.
What I'd do is load into a temp table as @Kolink suggests but with additional columns. Add a SET
clause as you load the data from XML.
CREATE TEMP TABLE person_xml LIKE person;
ALTER TABLE person_xml
ADD COLUMN FirstName VARCHAR(40),
ADD COLUMN LastName VARCHAR(40),
ADD COLUMN PersonId INT;
LOAD XML LOCAL INFILE 'person.xml' INTO TABLE person_xml
SET person_id = PersonId, fname = FirstName, lname = LastName;
SELECT * FROM person_xml;
+-----------+--------+-------------+-----------+-------------+----------+
| person_id | fname | lname | FirstName | LastName | PersonId |
+-----------+--------+-------------+-----------+-------------+----------+
| 1 | Mikael | Ronstr?m | Mikael | Ronstr?m | 1 |
| 2 | Lars | Thalmann | Lars | Thalmann | 2 |
+-----------+--------+-------------+-----------+-------------+----------+
Then copy to the real table, selecting a subset of columns.
INSERT INTO person SELECT person_id, fname, lname FROM person_xml;
Alternatively, drop the extra columns and use SELECT *
.
ALTER TABLE person_xml
DROP COLUMN PersonId,
DROP COLUMN FirstName,
DROP COLUMN LastName;
INSERT INTO person SELECT * FROM person_xml;
SELECT * FROM person;
+-----------+--------+-------------+
| person_id | fname | lname |
+-----------+--------+-------------+
| 1 | Mikael | Ronstr?m |
| 2 | Lars | Thalmann |
+-----------+--------+-------------+
与恶龙缠斗过久,自身亦成为恶龙;凝视深渊过久,深渊将回以凝视…