{"id":1881,"date":"2019-03-06T00:00:00","date_gmt":"2019-03-05T23:00:00","guid":{"rendered":"https:\/\/wwwneu.strehle.de\/tim\/weblog\/archives\/2019\/03\/06\/1640-2\/"},"modified":"2025-07-31T21:36:49","modified_gmt":"2025-07-31T19:36:49","slug":"1640-2","status":"publish","type":"post","link":"https:\/\/www.strehle.de\/tim\/weblog\/archives\/2019\/03\/06\/1640-2\/","title":{"rendered":"Using XML to copy data from PostgreSQL to MySQL"},"content":{"rendered":"\n

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.<\/p>\n\n\n\n

I expected this to be a lot of work (including XSLT magic), but it was surprisingly easy: It turns out that PostgreSQL\u2019s 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.)<\/p>\n\n\n\n

Step 1: Export XML from PostgreSQL<\/h3>\n\n\n\n

Create a SQL file postgresql-xml-export.sql<\/code> that invokes query_to_xml()<\/a> for each table (there\u2019s table_to_xml() as well but query is more flexible) and writes the output to a file:<\/p>\n\n\n\n

\\pset format unaligned\n\\t\n\\o song.xml\nSELECT query_to_xml('select * from song', false, false, '');\n\\o songbook.xml\nSELECT query_to_xml('select * from songbook', false, false, '');\n<\/code><\/pre>\n\n\n\n
Run it via psql<\/code>:<\/p>\n\n\n\n
$ psql postgresdb -f postgresql-xml-export.sql\n<\/code><\/pre>\n\n\n\n
You should now see the XML files:<\/p>\n\n\n\n
$ ls *.xml\nsong.xml  songbook.xml\n<\/code><\/pre>\n\n\n\n
Make sure they contain the right data. PostgreSQL uses a \/table\/row\/COLUMNNAME<\/code> format by default. Example:<\/p>\n\n\n\n
<table xmlns:xsi=\"http:\/\/www.w3.org\/2001\/XMLSchema-instance\">\n<row>\n  <id>song-119<\/id>\n  <fulltitle>Amazing grace<\/fulltitle>\n  <language>en<\/language>\n<\/row>\n<\/table>\n<\/code><\/pre>\n\n\n\n
Step 2: In MySQL, create the database schema and import XML<\/h3>\n\n\n\n
Make sure you have a MySQL database to import into. Use the utf8mb4<\/code> character set unless you know exactly what you\u2019re doing. I created a test database:<\/p>\n\n\n\n
CREATE DATABASE importtest CHARACTER SET utf8mb4;\n<\/code><\/pre>\n\n\n\n
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<\/a>. (That\u2019s not what you might want to do.)<\/p>\n\n\n\n
Here\u2019s my mysql-import-xml.sql<\/code> file:<\/p>\n\n\n\n
DROP TABLE IF EXISTS song;\n\nCREATE TABLE song (\n    id VARCHAR(128) NOT NULL,\n    fulltitle VARCHAR(255) NOT NULL,\n    language VARCHAR(10)\n);\n\nDROP TABLE IF EXISTS songbook;\n\nCREATE TABLE songbook (\n    id VARCHAR(128) NOT NULL,\n    name VARCHAR(255) NOT NULL\n);\n\nLOAD XML LOCAL INFILE 'song.xml' INTO TABLE song ROWS IDENTIFIED BY '<row>';\nLOAD XML LOCAL INFILE 'songbook.xml' INTO TABLE songbook ROWS IDENTIFIED BY '<row>';\n<\/code><\/pre>\n\n\n\n
Run that through mysql<\/code>:<\/p>\n\n\n\n
$ mysql -uUSER -pPASSWORD importtest < mysql-xml-import.sql\n<\/code><\/pre>\n\n\n\n
\u2026 and all the data is in the MySQL database. Pretty nice, isn\u2019t it?<\/p>\n","protected":false},"excerpt":{"rendered":"
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 […]<\/p>\n","protected":false},"author":1,"featured_media":0,"comment_status":"closed","ping_status":"","sticky":false,"template":"","format":"standard","meta":{"footnotes":"","_share_on_mastodon":"0"},"categories":[1],"tags":[],"class_list":["post-1881","post","type-post","status-publish","format-standard","hentry","category-weblog"],"share_on_mastodon":{"url":"","error":""},"_links":{"self":[{"href":"https:\/\/www.strehle.de\/tim\/wp-json\/wp\/v2\/posts\/1881","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/www.strehle.de\/tim\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/www.strehle.de\/tim\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/www.strehle.de\/tim\/wp-json\/wp\/v2\/users\/1"}],"replies":[{"embeddable":true,"href":"https:\/\/www.strehle.de\/tim\/wp-json\/wp\/v2\/comments?post=1881"}],"version-history":[{"count":1,"href":"https:\/\/www.strehle.de\/tim\/wp-json\/wp\/v2\/posts\/1881\/revisions"}],"predecessor-version":[{"id":1892,"href":"https:\/\/www.strehle.de\/tim\/wp-json\/wp\/v2\/posts\/1881\/revisions\/1892"}],"wp:attachment":[{"href":"https:\/\/www.strehle.de\/tim\/wp-json\/wp\/v2\/media?parent=1881"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/www.strehle.de\/tim\/wp-json\/wp\/v2\/categories?post=1881"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/www.strehle.de\/tim\/wp-json\/wp\/v2\/tags?post=1881"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}