Sample data for import and xml export - mysql and postgres (part 3 of 3)

Following up on previous postings I begin by giving the full xml output which should be pastable to your computer and then imported to any database with an xml import facility.

amd_bang_per_watt__20090720.xml
amd_bang_per_watt__20090720.xml-mit-create

or just a couple of rows as appeared in the previous posting:
amd_bang_per_watt__20090720.xml-select

For two of the main open source databases I use, some graphical tools are listed here:

• mysql-admin for mysql with source available direct from mysql.com
• pgadmin3 for postgres with source availability mentioned in news.

mysql-admin documentation available in English, Greek, Italian, Portuguese, Russian, Spanish, Ukrainian.

If you like a screenshotted introduction to the gui tools then here is a quick idea of the look and feel:

The version 6 release of mysql (maybe 2010) will include
load xml infile

will be a step forward. Version 6 will follow version 5.4 of mysql which is in testing as I write.

Commercial releases of mysql might show slightly different versions but in keeping with the title of this blog I tend to focus on gpl or free and open source flavours of software.

If you have the data in a variety of formats (usually the case if it is your data) then csv format and the formats mysqlimport has supported for the last 10 years should do fine to get the job done.

All major databases will likely soon offer xml import (if not already) which should help data interchange between retailers and suppliers providing xml standards are strictly adhered to. Newer xml proposed standards have public pages for further research at w3.org

If you are itching for bringing in xml data directly to mysql today then you might want to browse the following:

• xml inside a mysql column and later retrieval
• xml importing using python (2003ish)
  
• xml importing using perl libraries (2003ish)
• xml importing using a stored procedure xmldump_load
  

And now back to the data which I give in mysql own .sql format to aid anyone wanting to just load it directly rather than getting hung up on xml.

• amd_power_dissipation__20090721.sql
• amd_power_dissipation__20090721.sql-compact
  

So at whatever point you have joined this posting thread you can quickly pick up from here if you are interested in what comes next which is all Postgres.

Before leaving mysql entirely we should export the data using the --compatible flag, so as the sample data and postgres postings can discuss what (if anything) must be done to the sql output from --compatible to make it totally postgres.

Here is what mysql produces from --compatible=postgresql:

• amd_bang_per_watt.mysqldump4postgresSansCreate
  
• [ data only (no create statement) ]
• amd_bang_per_watt.mysqldump4postgresSansOpts
  
• [ data+create (less compact form) ]
  
• amd_bang_per_watt.mysqldump4postgres
  
• [ data+create (compact form) ]

Mark Slade had this to say regarding what to expect in his article about converting databases.

Despite how it sounds, this won't generate PostgreSQL compatible code, but it will get you halfway there.

Halfway sounds pretty good if you do not want to go down the 'csv out csv in' route. I explore both ways in the postgres postings.