The server is Linux but the development machine is Windows so I'm using psql command line. I tried \i C:/Users/John/Desktop/Parts.xml which did not work so either \i doesn't work with XML or I'm missing some syntax.
I have an empty database (and the user has permissions as far as i can tell). How do I import XML and have it's columns automatically created based on the column defined in the XML file? The XML file is an export of a Microsoft Access 2010 database table.
Best Answer
I was able to automate the process enough, keyword being enough. To help anyone else dealing with exporting from Microsoft Access and importing to PostgreSQL I'm providing details that helped us work through the errors we encountered. While I'm fairly competent with MySQL and new (though still reasonably well at PostgreSQL) the people who setup the various Microsoft Access databases didn't setup the databases/tables/columns well (understatement).
First download "MS Access to PostgreSQL", it's freeware and I plan to make a donation to them. Currently on their download page the download link is on the right side of the table, threw me off for a little bit.
http://www.bullzip.com/products/a2p/info.php
Secondly I needed to download the "2007 Office System Driver: Data Connectivity Components" which works fine with Office 2010...
http://www.microsoft.com/en-us/download/details.aspx?id=23734
Thirdly I opened up MS Access to PostgreSQL and followed the directions having the data imported to an existing empty database. Keep in mind that the program (at least by default) will spawn errors if there are tables already there with the same names (it won't automatically append/overwrite). Thankfully it provides a checkbox list of tables so when you've confirmed via psql or pgAdmin III (you must click on the left column before pressing F5 or it won't update the database/tables information) that at least some of the data has been imported it is time to deal with errors.
There were two error types that I've encountered for the most part thus far with just two separate databases. For accounting the person who setup Access used a proprietary
currencytype for some of the columns, the program didn't like that so I was able to maintain the row values by simply converting it to thetexttype. I'm correcting the database structure and all that after I've freed it from Microsoft's shenanigans as I'm trying to reduce the complexity of exporting it. You can change the column type by choosing the table, then clicking on the Fields menu in Access 2010, then under the secondary Formatting hierarchy you'll find "Display Type:".Apparently the program also does not like it when a column shares the same name as it's table so I simply added a prefix or suffix underscore and make note of it to correct after the dubious process of migrating the data is complete.
In general a necessary practice in Access is to right-click on the table name under the Tables object list, at the bottom of the context menu should be a Check Web Compatibility menu item. If it finds errors it will create a whole new table in the database; in Access 2010 URLs to the Microsoft website were clickable.
Another error I encountered was binary data, an image that exceeded the programs ability to import. I exported the data as XML and browsed to determine which row had the data, found the ID in Access and removed it. You can save it (in the Microsoft way at least) by copying the row itself and paste it in to Word...how to export it as a normal image I'm not sure offhand.
The following are my notes that I've written down to quickly repeat the process to get Access data migrated to PostgreSQL. I originally was going to just export as XML and import to PostgreSQL though I was unable to find a working solution in that manner and I would have still have had to deal with some of the issues mentioned here.
Access to PostgreSQL - Import Notes
1.) Use freeware "MS Access to PostgreSQL".
2.) When encountering errors use MS Access table web compatibility check (right click table).
3.) If further stumped...
A.) Allow import to create columns.
B.) Export Access to Excel.
C.) Export Excel to CSV/DOS.
D.) Ensure that the first id is NOT 0, must be 1 (may be subjective though).
4.) If table data import success then repeat step 1 but unselect trouble table AND already imported tables.