<blockquote> XML isn't really that widely accepted yet, it will be but not yet
</blockquote>
I would argue that XML is extremely well accepted, esp. within the Java community. But that's not really too important here. We recently completed a complex data migration from a non-relational sql server schema to an ACS object-oriented oracle schema. We used XML as an intermediate representation for the data. The major advantage was that all data was loaded thru our api. The major disadvantage was a serious performance penalty. If people are interested, I'd be more than happy to share more details of what we did.
With any serious data migration, where serious means having enough data that you're worried about the performance of the one time migration, we really need to consider native tools for doing the migration. Oracle provides a loader utility which is much more efficient than loading data through SQL.
For testing upgrade scripts, the problem is extremely difficult and has two distinct pieces:
1. Is the structure of the upgraded schema correct?
2. Did all my data make it to the right place?
The first question is much easier to test. For Oracle, I wrote a pl/sql script awhile ago which compares most of the objects in two different schemas. This package ensures that the same objects exist in both schemas and then does different validation based on the type of the object:
INDEX (existence check only)
LOB (existence check only)
PACKAGE, PACKAGE BODY, FUNCTION, PROCEDURE (line-by-line check)
SEQUENCE (check for identical next values)
TABLE (check for same columns, same constraints, same number of rows)
TRIGGER (check for same type, table, when_clause, text)
VIEW (check for same number of rows, same text)
I'm not sure if this script ever made it into ACS, but I'd be more than happy to dig it up if somebody thinks it will be useful. I found it very useful to validate that a version 1.0 schema + an upgrade script is equivalent to a version 1.1 schema.
The second point (validating that the data made it) is much more difficult - I don't know of any good tools/ways to automate even parts of this validation.
