|Application Architecture XML Loading Advice Requested: [message #93186]
|Sun, 06 July 2003 10:25
Registered: July 2003
Location: San Francisco, California
Note: This is being posted on both the ‘Data Warehouseing’ and ‘XML’ forums.
The Current System:
I'm supporting an application that currently receives an XML document every 5 minutes via an rftf channel on a UNIX box, parses the XML document using a series of KORN shell scripts (Abi Initio) and then store the data in single table in an Oracle 9i database for querying - not for Web Publication. Today the amount of data being stored is not much but the data volume has the potential to reach data warehouse levels.
The single Oracle table already has over 100 optional columns and is expected to grow. The XML document has multiple of optional subsections and additional optional subsections will be added on an ongoing basis. Everytime a new subsection is added or an existing subsection is changed, I must alter the Oracle schema and the KORN shell scripts.
Does the technology exist, and at a relatively low of complexity, to utilize Oracle 9i’s XML Schema features and Oracle’s XML PL/SQL storing and retrieving capabilities in an effort to minimize future XML document loading rework? Also on the table is the question of weather to split the Oracle table into one parent with many leaves/children, but it an XML Schema is decided the best option, then of course the current table will need to be split. The little bit of research I’ve done on Oracle and XML appears that the XML loading and storing in Oracle is most often used when the output of the queries needs to be published to the web. This is not the case here. I am simply looking for a dynamic document storing solution into a database, as the customers are use to query Oracle for their information.
Any help would be greatly appreciated.