PURPOSE
-------
This article explains the difference between sys.xmltype and xmltype datatypes.
SCOPE & APPLICATION
-------------------
This article is written for XML developers and Oracle DBAs.
SYS.XMLType vs. XMLType Datatypes
----------------------------------
When creating a table with an XMLTYPE column, you can create the column as XMLTYPE or as SYS.XMLTYPE
A subsequent DESC on the table will show the column as either XMLTYPE or SYS.XMLTYPE
What is the difference, and when should SYS/XMLTYPE be used and when should XMLTYPE be used ?
There is only one type by the name of '%XMLTYPE%' within the database, for example:
SELECT TYPE_NAME, OWNER FROM DBA_TYPES WHERE TYPE_NAME LIKE '%XMLTYPE';
TYPE_NAME OWNER
------------------------------ --------
XMLTYPE SYS
XMLTYPE is a synonym for SYS.XMLTYPE, as shown below :-
SELECT SYNONYM_NAME, OWNER FROM DBA_SYNONYMS WHERE SYNONYM_NAME LIKE '%XMLTYPE%';
SYNONYM_NAME OWNER
------------------------------ ------------------------------
XMLTYPE PUBLIC
In summary, you may use SYS.XMLTYPE or XMLTYPE in a table column definition.
There is however an exception to the rule in Oracle 9.2, when attempting to use SQLLoader to load an XMLTYPE column, due to Abstract: SQL*LOADER-418 WHILE LOADING XMLTYPE COLUMN BASED ON XMLTYPE SYNONYM (currently not published)
Workaround:
use only sys.xmltype to create xmltype column/table
create table po5(po sys.xmltype);
Fixed In Ver: 10.0.0
RELATED DOCUMENTS
-----------------
Oracle XML Database online documentation