Today, I needed to copy a few tables from a PostgreSQL database into a MySQL database. I wanted to use XML files to exchange the data so I could inspect and validate it easily.

I expected this to be a lot of work (including XSLT magic), but it was surprisingly easy: It turns out that PostgreSQL’s default XML output format is supported by MySQL out of the box! (And has been for a while; I was doing this on PostgreSQL 9.2 and MariaDB 5.5.)

Step 1: Export XML from PostgreSQL

Create a SQL file postgresql-xml-export.sql that invokes query_to_xml() for each table (there’s table_to_xml() as well but query is more flexible) and writes the output to a file:

\pset format unaligned
\t
\o song.xml
SELECT query_to_xml('select * from song', false, false, '');
\o songbook.xml
SELECT query_to_xml('select * from songbook', false, false, '');

Run it via psql:

$ psql postgresdb -f postgresql-xml-export.sql

You should now see the XML files:

$ ls *.xml
song.xml  songbook.xml

Make sure they contain the right data. PostgreSQL uses a /table/row/COLUMNNAME format by default. Example:

<table xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance">
<row>
  <id>song-119</id>
  <fulltitle>Amazing grace</fulltitle>
  <language>en</language>
</row>
</table>

Step 2: In MySQL, create the database schema and import XML

Make sure you have a MySQL database to import into. Use the utf8mb4 character set unless you know exactly what you’re doing. I created a test database:

CREATE DATABASE importtest CHARACTER SET utf8mb4;

Wanting to delete all existing data on import, I wrote a single SQL script that drops and recreates the tables (I wrote the DDL manually), then imports the data via LOAD XML. (That’s not what you might want to do.)

Here’s my mysql-import-xml.sql file:

DROP TABLE IF EXISTS song;

CREATE TABLE song (
    id VARCHAR(128) NOT NULL,
    fulltitle VARCHAR(255) NOT NULL,
    language VARCHAR(10)
);

DROP TABLE IF EXISTS songbook;

CREATE TABLE songbook (
    id VARCHAR(128) NOT NULL,
    name VARCHAR(255) NOT NULL
);

LOAD XML LOCAL INFILE 'song.xml' INTO TABLE song ROWS IDENTIFIED BY '<row>';
LOAD XML LOCAL INFILE 'songbook.xml' INTO TABLE songbook ROWS IDENTIFIED BY '<row>';

Run that through mysql:

$ mysql -uUSER -pPASSWORD importtest < mysql-xml-import.sql

… and all the data is in the MySQL database. Pretty nice, isn’t it?