Welcome to OStack Knowledge Sharing Community for programmer and developer-Open, Learning and Share
Welcome To Ask or Share your Answers For Others

Categories

0 votes
1.2k views
in Technique[技术] by (71.8m points)

mysql - LOAD XML LOCAL INFILE with Inconsistent Column Names

MySQL has a nice statement: LOAD XML LOCAL INFILE

For example, if you have this table:

 CREATE TABLE person (
    person_id INT NOT NULL PRIMARY KEY,
    fname VARCHAR(40) NULL,
    lname VARCHAR(40) NULL
 );

and the following XML file called person.xml:

<list>
      <person>
          <person_id>1</person_id>
          <fname>Mikael</fname>
          <lname>Ronstr?m</lname>
      </person>
      <person>
          <person_id>2</person_id>
          <fname>Lars</fname>
          <lname>Thalmann</lname>
      </person>
</list>

You can do this:

LOAD XML LOCAL INFILE 'person.xml'
INTO TABLE person
ROWS IDENTIFIED BY '<person>';

My question is, what if the column names were different in the XML file than they are in the table? For example:

<list>
      <person>
          <PersonId>1</PersonId>
          <FirstName>Mikael</FirstName>
          <LastName>Ronstr?m</LastName>
      </person>
      <person>
          <PersonId>2</PersonId>
          <FirstName>Lars</FirstName>
          <LastName>Thalmann</LastName>
      </person>
</list>

How can you accomplish the same thing with a MySQL statement without manipulating the XML file? I searched everywhere but couldn't find an answer.

See Question&Answers more detail:os

与恶龙缠斗过久,自身亦成为恶龙;凝视深渊过久,深渊将回以凝视…
Welcome To Ask or Share your Answers For Others

1 Answer

0 votes
by (71.8m points)

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

与恶龙缠斗过久,自身亦成为恶龙;凝视深渊过久,深渊将回以凝视…
Welcome to OStack Knowledge Sharing Community for programmer and developer-Open, Learning and Share
Click Here to Ask a Question

...