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