I've got 35 million highly structured XML documents, with a total size of 500 GB. An XSD is available for this data, and the end goal is to transform the data into an RDB. All fields can be considered varchar, number, or date; except maybe one or two CLOB fields. The XSD structure contains a lot of 1-n relationships, and sub-nested structures, so is quite complex.
The data has to get loaded once, and then transformed into an RDB once, so speed of update/insert queries is irrelevant. Also the speed of loading/index creation is not so important, since it only has to run once. As long as it finishes within a week or two i'm happy.
At the moment I'm setting up the XMLDB by registering the XSD as Object Relational, with create objects and create tables set to true. For now, I haven't added any other significant annotations to the XSD, and I'm only loading about 50MB's of data as a test. I'm using a whole set of XMLTable/flwor queries to transform the XML data into RDB tables. I fear scaling this to 500 GB will cause queries that run for eternity.
As I'm quite new to XMLDB, and there are -a lot- of possible tweaks and settings in the XMLDB documentation, I'm wondering if there are some crucial features/annotations/optimizations.... i should be looking out for that can have an impact on the overall speed of the final queries, or other parts of the process.
This is a very broad question, but, any tips are welcome, and if there's more information that might be relevant, ask away!
