What is the difference between these two create table statements?

Question

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?

Answer 1

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.

Answer 2

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