Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

a)

SQL> CREATE TABLE xml_tab1(poDoc XMLTYPE);

Table created.

SQL> desc xml_tab1;
 Name                                      Null?    Type
 ----------------------------------------- -------- ----------------------------
 PODOC                                              PUBLIC.XMLTYPE

b)

SQL> CREATE TABLE xml_tab2 of XMLType;

Table created.

SQL> desc xml_tab2;
 Name                                      Null?    Type
 ----------------------------------------- -------- ----------------------------
 TABLE of PUBLIC.XMLTYPE

Code from here.

In the first one, we are creating a column of type XMLTYPE. What does the second one do? When should I use the first or the second one?

share|improve this question

2 Answers 2

up vote 6 down vote accepted

The second statement is creating an object table.

It is almost never the case that you really want to use an object table. That was something that was introduced in the 8i time frame when Oracle was making the database object oriented. While a lot of the object oriented PL/SQL enhancements have been useful, using object types in SQL is not something that I'd recommend.

share|improve this answer

...but then again... XML has nothing to do with SQL, so some normal reasoning might not apply...(regarding object types for example).

The first example is an XMLType and depending on your database version the underlying store definition might be Securefile CLOB or Securefile Binary XML. The first one is deprecated from Oracle 12c and onwards due to non-optimal performance reasons.

The second example (XMLtype Table) has, if the syntax is not more specific as above, also Securefile CLOB or Securefile Binary XML.

The XMLType column example is more "SQL" native in its use (insert XML into column, select * from table where xmlcolumn etc) , were the XMLType table is more XML native (insert XML into XML table, (X)query "doc" usage).

XMLtype tables can be easily used with the XMLDB XDB Repository ("hierarchy enabled"), that is can be used with automatically XML schredding via for example FTP, WebDaV or Queried via HTTP access. Do to this behavior extension, they are also protected via XMLDB ACL security implementations.

XMLType columns can have the same FTP/WebDAV/HTTP characteristics but implementation of this functionality is not so "out of the box" as with the XMLType table example.

BTW Oracle object orientated functionality was introduced in Oracle 8.0.

Both examples above, in their current form, will result in securefile alternatives which have nothing to do with the Object Orientated functionality.

Have a look at the differences in use cases of XMLType and more on the main XMLDB homepage

Oracle XML DB in Oracle Database 12c, June 2013
Best Practices for XQuery processing in Oracle Database, 12c June 2013
Best Practices for XML Storage in Oracle Database, 12c Oct 2013

http://www.oracle.com/technetwork/database/database-technologies/xmldb/overview/index.html

More info and howto information can be found on the Oracle XMLDB forum and/or on www.xmldb.nl (for example http://www.liberidu.com/blog/2007/06/24/oracle-11g-xmltype-storage-options/ to see some differences regarding the syntax differences between the XMLType storage options)

HTH

share|improve this answer

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.