StackOverflow2013

Note that there are some explanatory texts on larger screens.

plurals

PO
text
Body
copied!<p>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.</p> <p>What I'd do is load into a temp table as @Kolink suggests but with additional columns. Add a <code>SET</code> clause as you load the data from XML. </p> <pre><code>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 | +-----------+--------+-------------+-----------+-------------+----------+ </code></pre> <p>Then copy to the real table, selecting a subset of columns.</p> <pre><code>INSERT INTO person SELECT person_id, fname, lname FROM person_xml; </code></pre> <p>Alternatively, drop the extra columns and use <code>SELECT *</code>.</p> <pre><code>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 | +-----------+--------+-------------+ </code></pre>

Querying!

Guidance

An individual column

Larger individual text columns get their own page to allow for proper reading.

SQuiL has stopped working due to an internal error.

If you are curious you may find further information in the browser console, which is accessible through the devtools (F12).

Reload