The query below lists:

(A) all columns with XML data types accessible to the current user in Oracle database

(B) all columns with XML data types in Oracle database.

Query

A. All columns with XML data types accessible to the current user

select col.owner as schema_name,
       col.table_name,
       column_id,
       column_name,
       data_type
from sys.all_tab_cols col
join sys.all_tables tab on col.owner = tab.owner
                        and col.table_name = tab.table_name
where col.data_type = 'XMLTYPE'
      and col.owner not in
      ('ANONYMOUS','CTXSYS','DBSNMP','EXFSYS', 'LBACSYS', 
      'MDSYS', 'MGMT_VIEW','OLAPSYS','OWBSYS','ORDPLUGINS', 'ORDSYS',
      'SI_INFORMTN_SCHEMA','SYS','SYSMAN','SYSTEM', 'TSMSYS','WK_TEST',
      'WKPROXY','WMSYS','XDB','APEX_040000', 'APEX_PUBLIC_USER','DIP', 
      'FLOWS_30000','FLOWS_FILES','MDDATA', 'ORACLE_OCM', 'XS$NULL',
      'SPATIAL_CSW_ADMIN_USR', 'SPATIAL_WFS_ADMIN_USR', 'PUBLIC',
      'OUTLN', 'WKSYS', 'APEX_040200')
order by col.owner,
         col.table_name,
         column_id;

B. All columns with XML data types

select col.owner as schema_name,
       col.table_name,
       column_id,
       column_name,
       data_type
from sys.dba_tab_cols col
join sys.dba_tables tab on col.owner = tab.owner
                        and col.table_name = tab.table_name
where col.data_type = 'XMLTYPE'
      and col.owner not in 
      ('ANONYMOUS','CTXSYS','DBSNMP','EXFSYS', 'LBACSYS', 
      'MDSYS', 'MGMT_VIEW','OLAPSYS','OWBSYS','ORDPLUGINS', 'ORDSYS',
      'SI_INFORMTN_SCHEMA','SYS','SYSMAN','SYSTEM', 'TSMSYS','WK_TEST',
      'WKPROXY','WMSYS','XDB','APEX_040000', 'APEX_PUBLIC_USER','DIP', 
      'FLOWS_30000','FLOWS_FILES','MDDATA', 'ORACLE_OCM', 'XS$NULL',
      'SPATIAL_CSW_ADMIN_USR', 'SPATIAL_WFS_ADMIN_USR', 'PUBLIC',
      'OUTLN', 'WKSYS', 'APEX_040200')
order by col.owner,
         col.table_name,
         column_id;

Columns

• schema_name - name of the schema (owner)
• table - name of the table
• column_id - column position in table
• column_name - name of the column
• data_type - type of spatial data

Rows

• One row represents one column with a XML data type
• Scope of rows: all columns containing XML data types in the database
• Ordered by schema name, table name and column position in table

Sample results