Tuesday, July 21, 2009

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.


or just a couple of rows as appeared in the previous posting:

For two of the main open source databases I use, some graphical tools are listed here:
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:

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.
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:
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.

No comments: