Re: Oracle Newbie: XML Handling in Oracle 9i

The answer depends on whether you need to losely or tightly couple the processing.

Between two independent systems, defining the 'public' interfaces in an XML schema, and then wrapping the 'payload' of a call in an XML document can be useful, especially if the systems are maintained by two different groups, and the interface changes rapidly.

A prime example of this are multiple order entry systems feeding purchase orders into a single fufilment system. The PO's could actually come from systems from completely different companies, to a single suppliers system. The supplier system could publish a schema which defines the structure it will accept PO's in, and the different order entry systems could all use this schema. This is an example of a losely coupled system environment.

As an aside, in this situation, with Oracle, I wouldn't actually 'pass' the XML document between the systems. I would have my first system (Order Entry), actually store the document in the fufilment database against the XML schema, and then call the fufilment system with some reference to the document. This could be done via FTP, HTTP post, WEBDav, load from a text file, dequeue from a message queue, or even via direct XML apis. The advantages to this is that there is now a history of the interaction between the two systems, and a single version of the truth.

Note that in these types of environments the call could be synchronous, but is more often asnychronous, and in fact the payload (the PO), could also include directions as to how the fufilment system processes the actual PO itself - and all this can be part of the XML schema that defines the interface between the systems.

My backend OE system would then simply access the document, using the same shared XML schema definition, and process accordingly.

On each side of the interface, each system will need to effectively 'parse' the XML document (or relevant bits of the XML document). You could use standard XML apis to do this, or in Oracle XML DB, use standard SQL, and the database under the covers will effectively do the parsing for you.

As explained, whether you use XML apis, or SQL, the Oracle database is doing every thing it can for you to make sure you get the most performant 'parsing' of the XML doc, by storing it in a 'pre-parsed' format, and supporting such things as lazy DOM materialization, etc. So the performance, in this scenario, will not be an issue.

But the same technique within a single system ? - No - because by definition the interfaces within a system are tightly coupled. By imposing a loosely coupled model you are simply adding an obfustication layer around an interface where it is not neeed. Instead I would build my SP's so that they are modular and encapsulated, so that changes to columns etc do not unnecessary effect the SP's. Oracle's PL/SQL has many features that directly support this approach to encapsulation and modularization - and these featurs, coupled with a good solid understanding of generic programming techniques, will protect you as much as possible from the impacts of changes in the underlying table storage. Have a look in the documentation at topics such as PACKAGES, ROW TYPES etc. There are many examples.

So the short answer - XML defined interfaces between stored procedures within the same system ? - No. Between public interfaces in disparate, seperated systems ? - Yes. And a schema provides an easy way to manage the public declarations of these interfaces. Received on Fri Jan 31 2003 - 09:34:32 CST