I asked Sean Dillon, our local XML guy, to take a look at this and here's what he had to say:



Hi Jon,

You won't find this question addressed in a single paragraph, because the answer simply isn't cut and dry. There are a variety of ways you can store your XML, and the best methodology to use depends heavily on how you're using the XML. You could go with any number of ways, you'll just get more mileage if you take some time to architect the storage according to your requirements.

It sounds like you're talking about XMLType columns (or XMLType tables, essentially the same storage architecture). You can choose a few different ways to store these.

1. The first way I'll mention has you doing all the storage yourself. You don't store the data in an XMLType column, don't store it as a CLOB, you parse the document and shred the scalar values into relational tables. If you ever want to reconstitute the XML document, you need to generate it yourself, ensure that the data has not changed, make sure none of the data (rows, etc) have been deleted, account for white space and/or processing instructions if those are important to you, etc. This way is the most labor-intensive, the most risky, many times will not buy you any performance gain in loading, but will give you the most control over where the scalar values of your elements & attributes are stored.

2. The next way is in an XMLType column, stored as a CLOB (under the covers). You can create an XMLType column as a part of a table, an XMLType table itself, or simply upload XML documents into the XML DB Repository. In any of these cases, you have the option of Oracle storing the underlying data as a CLOB instead of shredding the document into an object-relational sub-structure (more about that in a minute). This storage mechanism is good if you're intesterested in complete document fidelity (you can't lose a single whitespace character from the document, for instance), it's good if you will only ever deal with the document as a whole (in which case there's no need to shred the document b/c Oracle will always have to reconstitute the entire document for you to use it), and if your searches are known well in advance and you plan on indexing your search paths. If you don't index search paths with CLOB-based storage, you're parsing EVERY document in the table whenever you search for information in that XML document (that is, unless you choose to use Oracle Text, which I strongly advise you DO) :).

3. Lastly, you can store the XMLType column in an object-relational storage architecture. This means that when you load the XML document, Oracle automatically shreds the document into objects and relational rows for you, behind the scenes. When you want to pull out the entire XML document, Oracle rebuilds the document for you automatically. When you search through your XML documents, Oracle is able to go directly to the underlying storage mechanism and search only those objects or tables instead of having to load up a DOM tree every time it needs to qualify rows for a select or constraint. When you query particular elements or attributes, Oracle is able to go directly to the underlying storage and pull out ONLY that data, rather than having to load up an in-memory DOM tree and parsing out that data on the fly (translation: potentially nasty performance).

Keep in mind, the XMLType data type has a couple of different storage architecture options, but once this is chosen it's COMPLETELY transparent to the application and user. You could, quite literally, choose to change your storage architecture six months from now, and your applications wouldn't have to change one bit in order to accomodate this change. It WOULD require a data reload, mind you, but just to illustrate the concept, an xpath search executed on a clob-based xmltype column can be executed against an object-relational-based xmltype column, get the exact same results, the performance will differ based on how you've decided to build the storage.

This may seem complex, but it's really not. If you choose to use object-relational storage, Oracle is capable of COMPLETELY building this for you, there's no need for you at all to figure out all the objects and keys under the covers. You have the option of doing so, and any DBA I've ever met who cares about their system performance would most definitely take advantage of the ability to modify this "behind-the-scenes" storage architecture, but it's not mandatory.

Or... ofcourse... if simplicity is your ultimate goal just declare the column of XMLType and load away. This ends up in CLOB-based storage, which may or may not be ideal, but IT'S DEFINITELY SIMPLE :).

There is a pro/con chart in the Oracle Database Developer's Guide - XML DB that outlines what the pros and cons are for each different storage architecture. You can find this documentation on OTN. See Chapter 4, pgs 4-5 & 4-6, Table 4-1:

Feature LOB Storage (with Oracle Text index) Structured Storage (with B*Tree index)
1. Database schema flexibility
LOB Storage - Very flexible when schemas change.
Structured Storage - Limited flexibility for schema changes. Similar to the ALTER TABLE restrictions.
2. Data integrity and accuracy
LOB Storage - Maintains the original XML byte for byte - important in some applications.
Structured Storage - Trailing new lines, whites pace within tags, and data format for non-string datatypes is lost. But maintains DOM fidelity.
3. Performance
LOB Storage - Mediocre performance for DML.
Structured Storage - Excellent DML performance.
4. Access to SQL
LOB Storage - Some accessibility to SQL features.
Structured Storage - Good accessibility to existing SQL features, such as constraints, indexes, and so on
5. Space needed
LOB Storage - Can consume considerable space.
Structured Storage - Needs less space in particular when used with an Oracle XML DB registered XML schema.

Hope that helps!