Skip to Main Content


Question and Answer

Tom Kyte

Thanks for the question, Jon.

Asked: April 30, 2004 - 9:02 am UTC

Last updated: July 13, 2007 - 1:36 pm UTC

Version: 9.2.0

Viewed 1000+ times

You Asked

I have a followup question (I read the chapter and didn't see this addressed). Is it fair to say that it is significantly easier to load sophisticated XML into a single table that are based upon Oracle Objects using the Oracle XML utilities than it is to load them into multiple relational tables which require system generated keys to link them?

Thank in advance.

and Tom said...

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!


  (19 ratings)

Is this answer out of date? If it is, please let us know via a Comment


What About Materialized Views?

Riad Alshaalan, July 10, 2004 - 6:22 am UTC

Good answer for a good question, but what about materialized view refresh options for queries accessing XMLType data?

Tom Kyte
July 10, 2004 - 9:21 am UTC

what do you mean?

XMLType and Materialized Views

Riad Alshaalan, July 10, 2004 - 11:09 am UTC

Can I create a fast refresh materialized view for a query that joins/aggregates rows of XMLType (i.e. a query containing EXTRACT(xpath))?

Tom Kyte
July 10, 2004 - 8:50 pm UTC

give us an example (hint hint -- trying to get you to walk down the path of trying it)....

give us the source table(s), the defining query and lets see where it goes.

Can't be Done

Riad Al-Shaalan, July 12, 2004 - 10:47 am UTC

It popped into my head that since xmltype is a clob it cannot be included in a materialized view log filter list.

Wouldn't it be nice if oracle allowed functions (such as extract(xmltype, xpath)) in a materialized view column? It may not be enough, but its a start.

A reader, July 15, 2004 - 12:49 pm UTC

Why use XML. What is its purpose?

RAD Development

Riad Al-Shaalan, July 18, 2004 - 6:04 am UTC

Since XML is used extensibly as a data exchange format between web clients and middle tiers, as a webapp developer I imagine the ideal case where I could quickly have a working prototype by just throwing xml in a repository without having to shred it into relational tables up front (i.e. RAD Development).

I see fast refresh views of relational tables (especially aggregate views) as a great RAD tool, and they would be even greater RAD tools if they directly supported XML repositories.

Tom Kyte
July 18, 2004 - 12:26 pm UTC

well, since any query can be materialized as XML simply from the database - not understanding the point here.

but throw the xml in there-- so? you'd be shredding it for your mv's at the end of the day.

To shred or not to shred

A reader, July 19, 2004 - 12:15 am UTC

Yeah, I guess I was being too far out. But still, fast materialized views for XQuery might hit the mark.

Last post by me (To shred or not to shred)

Riad Al-Shaalan, July 19, 2004 - 12:22 am UTC

Forgot to enter my name in the post. Sorry.

Common shredding issues

Adrian, January 13, 2005 - 11:29 am UTC

Tom (Sean)

Using XML DB, I've got as far as registering an XML schema to create the default object-relational tables and types and have also created the folder to upload my XML files to. Currently I'm trying to get one file to shred. Should I expect to be able to upload the .xml file and then query the O-R table directly to see it shredded ? Currently all I'm seeing is 0 rows. The .xml file *should* fit the .xsd, as that is what we publish.

A little bit more:- I added xsd:defaultTable and xsd:SQLType to the .xsd to control the O-R names, but other than that, the .xsd and .xml files are as original.

Any ideas where I should be looking for clues?


Tom Kyte
January 13, 2005 - 12:26 pm UTC

Hi Adrian, Sean here.

Yes, you shoould be able to upload the file into a folder in the XML DB repository, then query the OR tables that were created for that XML Schema. From the sound of it, the only thing I can conclude is the XML documents you're uploading don't have the XML Schema url in the documents themselves. This is what's used for associating an XML document to an XML Schema. When the repository gets a new file, it evaluates the schema association, and if that schema is registered it looks for the storage architecture. In this case, this is when Oracle should decide to parse the data and put the values into the OR table.

Hope that helps.


COMMIT was it !

Adrian, January 14, 2005 - 5:17 am UTC

Hi Sean,

Thanks for your response. Having spent time yesterday wondering where on earth the xml and xsd differed, it actually turned out to be a commit problem with using Windoze. When dropping the xml files into XML DB via a mapped web folder, we couldn't see the data, hence my question to you. However, using the DBMS_XDB.createResource procedure followed by a COMMIT solved the issue and the document was shredded as expected. Very cool stuff this XML DB !


A reader, February 21, 2005 - 11:20 am UTC

Hi Tom,
I am hearing a lot about xquery. In last two issue of oracle magazine there is an artical on xquery. Most of this is theoritical , no practical example.I searched your site on xquery and got this post.
I heard that xquery has been implemented in 10g.Could you please explain xqeury functionality with examples?
I would appreciate that


Tom Kyte
February 21, 2005 - 12:51 pm UTC

it is just an xml query language, nothing special or magical about it. sort of like our "contains" clause for searching documents.

google it, it is an "open" standard, nothing Oracle specific to it.

online support

shahnawaz husain, March 03, 2005 - 1:41 am UTC

dear sir ,
your question and answer are very useful but some time when we have emergency to find out answer ,then what is the process please tell me .

Tom Kyte
March 03, 2005 - 7:25 am UTC

anything that is "emergency related" would fall under the "support" banner.

</code> <code>

I do "how do I..." they do "uh oh"

I'm not support.

Examples or links to examples

Mark Wooldridge, June 15, 2005 - 3:28 pm UTC

Sean's response stated

'...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.'

The examples of how to modify this "behind-the-scenes" storage architecture use annotations in the schema. Can the storage be manually configured without annotating the schema? If so do you have any examples or links to examples?

Trying to understand ...

Greg, October 27, 2006 - 10:15 am UTC

Hi, was hoping I could get some help understanding this a bit more ..

I've read through the documentation, and fiddled with a couple examples, and have successfully done some creating/inserting/extracting and such of some XMLType columns in a dev schema.

However, there are a couple things I'm just still not grasping ... was hoping somebody could help explain.

I keep seeing the terms: "Structured Storage", "Non-structured Storage" and slightly related: "Schema Validation".

Ok, at first, I thought these were related somewhat, but I finally found something in the docs that pointed out I could have a Registered Schema, use Schema Validation all on an XML stored in an XMLType via Non-Structured storage ... (At least I think that's what it said .. heh )

Even though I read that, I'm still a bit confused by it ...

Let's start with Schema Validation (Cause I think that's gotta come first before the structured storage, right?)

Ok, so I Register a Schema, and Oracle uses an .xsd file I provide it to create a bunch of objects in a schema, correct?
Are these objects used to validate an XML later? (ala schema validation?) or are they just used to store the "Structured" XML ??

... I'll hold off tossing too many questions out, I might start understanding things more once I get there ... :)

Tom Kyte
October 27, 2006 - 6:17 pm UTC

well, the difference between structured and unstructured is basically: "are you having us shred the XML document instance upon insert and placing it into rows/columns in a bunch of Object tables - or are you using a CLOB"

The schema validation is like an integrity constraint, does the document you are inserting conform to (follow the rules of) some XML schema (XSD)

ok .. so ..

Greg, October 30, 2006 - 8:58 am UTC

Ok .. then I guess I'm not as far off understanding this as I thought ... but can you (or heck, someone else who reads this) help me on how to validate against a schema and still use unstructured?

From what I've seen .. once you "register" the schema (and it creates the objects ..), next time you pull in an xml and store it into your XMLType ... Oracle automagically shreds it into the object schema?

What if I don't want it to do that?
When/how do I "validate" it against the schema?
(The docs haven't been that clear on helping me piece these little tidbits out ... sorry .. )


Tom Kyte
October 30, 2006 - 9:41 am UTC

to go deep into XML, I'm going to suggest you use:

</code> <code>

Mark Drake is a serious expert on this stuff (product manager) and would be best suited to answer the questions...


Greg, October 30, 2006 - 10:56 am UTC

Thanx for your time, Tom!!!

how to do this

rahul, November 20, 2006 - 9:29 pm UTC

was wondering if anyone can help me out or if there is a way to do this in xml.
I have a xml of the form -

<RESPONSE ResponseDateTime="2006-11-16T11:46:37">
<KEY _Name="CustomerNumber" _Value="7303881"/>
<KEY _Name="TransactionNumber" _Value="001010021"/>
<KEY _Name="Type" _Value="UNDERWRITING_RESPONSE1.1"/>
<KEY _Name="CompanyID" _Value="00006"/>

I need to loop thru the KEY elements to parse the attributes whose Values depend on the Name.
in other words -
when CustomerNumber then 7303881
when TransactionNumber then 001010021
when Type then RESPONSE
when CompanyID then 00006.

I started this out with, but doesn't seem to do anything-

v_node_name varchar2(60);
v_cust_nbr varchar2(60);
v_TransactionNumber varchar2(60);
v_Type varchar2(60);
v_CompanyID varchar2(60);

v_node_name := xml.extract('/RESPONSE/KEY/@_Name').getstringval();
case when v_node_name = 'LoanNumber' then
v_cust_nbr := xml.extract('/RESPONSE/KEY/@_Value').getstringval();
when v_node_name = 'TransactionNumber' then
v_TransactionNumber := xml.extract('/RESPONSE/KEY/@_Value').getstringval();
when v_node_name = 'Type' then
v_Type := xml.extract('/RESPONSE/KEY/@_Value').getstringval();
when v_node_name = 'CompanyID' then
v_CompanyID := xml.extract('/RESPONSE/KEY/@_Value').getstringval();
end case;
end loop;

rename column name,

A reader, July 13, 2007 - 11:50 am UTC

I created a table in with xmltype and the column_name in user_lobs view has a weird name (SYS_***)

Is there a way to have a column name of my choice for the xmltype which I define in my create table?

I tried:

create table abc(a number, b sys.xmltype)
lob(b) store as (mysegment);

I get ora-00904 "b" invalid identifier.

however, if I have "clob" datatype instead of "xmltype", then it works.

Please let me know.


Tom Kyte
July 13, 2007 - 12:06 pm UTC

ops$tkyte%ORA10GR2> create table abc(a number, b sys.xmltype)
  2  /

Table created.

ops$tkyte%ORA10GR2> desc abc;
 Name                                Null?    Type
 ----------------------------------- -------- ------------------------
 A                                            NUMBER
 B                                            SYS.XMLTYPE

ops$tkyte%ORA10GR2> select b from abc;

no rows selected

cut and paste an example for us please.

To A reader

Michel Cadot, July 13, 2007 - 12:29 pm UTC

It is just a wrong error message in corrected in
SQL> create table abc(a number, b sys.xmltype) 
  2  lob(b) store as (mysegment); 
lob(b) store as (mysegment)
ERROR at line 2:
ORA-22853: invalid LOB storage option specification


follow up,

A reader, July 13, 2007 - 12:37 pm UTC

SQL> create table test_lob
2 (a number not null,
3 b number,
4 c varchar2(30),
5 d sys.xmltype
6 )
7 lob (d) store as my_lob;
lob (d) store as my_lob
ERROR at line 7:
ORA-00904: "D": invalid identifier

However, if I don't mention the last line of my previous create table statment then it works.

SQL> create table t_xmltype_tab (c sys.xmltype);

Table created.

SQL> create table c_lob_tab ( n number, c clob)
2 lob (c) store as segname;

Table created.

Now, if I query on user_lobs

SQL> select table_name,column_name,segment_name from user_lobs;

------------------------------ -------------------- ------------------------------
T_XMLTYPE_TAB SYS_NC00002$ SYS_LOB0000052860C00002$$

My question is how to have a proper name defined for xmltypes instead of having names like SYS_*****

Tom Kyte
July 13, 2007 - 1:36 pm UTC

ops$tkyte%ORA10GR2> create table t (c sys.xmltype)
  2  lob (c.xmldata) store as whatever
  3  /

Table created.

ops$tkyte%ORA10GR2> select table_name,segment_name from user_lobs where table_name = 'T';

TABLE_NAME                     SEGMENT_NAME
------------------------------ ------------------------------
T                              WHATEVER

ops$tkyte%ORA10GR2> desc t
 Name                                     Null?    Type
 ---------------------------------------- -------- ----------------------------
 C                                                 SYS.XMLTYPE

the column name you are interested in - is C in this example, the segment name you can control by referencing the clob in the xmltype.

More to Explore


Complete documentation on Securefiles and Large Objects here