VIEW: SYS.ALL_XML_SCHEMAS

Source

View Text - Preformatted

select u.name, s.xmldata.schema_url,
          case when bitand(to_number(s.xmldata.flags,'xxxxxxxx'), 16) = 16
               then 'NO' else 'YES' end,
	  case when bitand(to_number(s.xmldata.flags,'xxxxxxxx'), 16384) = 16384
               then xdb.dbms_csx_int.GetCSXSchema(xmltype(value(s).getclobval())) else value(s) end,
          xdb.dbms_xmlschema_int.xdb$Oid2IntName(s.object_id),
          case when bitand(to_number(s.xmldata.flags,'xxxxxxxx'), 16) = 16
               then s.xmldata.schema_url
               else 'http://xmlns.oracle.com/xdb/schemas/' ||
                    s.xmldata.schema_owner || '/' ||
                    case when substr(s.xmldata.schema_url, 1, 7) = 'http://'
                         then substr(s.xmldata.schema_url, 8)
                         else s.xmldata.schema_url
                    end
          end,
          case when bitand(to_number(s.xmldata.flags, 'xxxxxxxx'), 128) = 128
               then 'NONE'
               else case when
                    bitand(to_number(s.xmldata.flags, 'xxxxxxxx'), 64) = 64
                    then  'RESMETADATA'
                    else  'CONTENTS'
                    end
          end,
          case when bitand(to_number(s.xmldata.flags,'xxxxxxxx'), 16384) = 16384
              then 'YES' else 'NO' end,
          s.sys_nc_oid$,
          case when bitand(to_number(s.xmldata.flags,'xxxxxxxx'), 32768) = 32768
              then 'YES' else 'NO' end
    from user$ u, xdb.xdb$schema s
    where u.user# = userenv('SCHEMAID')
    and   u.name  = s.xmldata.schema_owner
    union all
    select s.xmldata.schema_owner, s.xmldata.schema_url, 'NO', value(s),
          xdb.dbms_xmlschema_int.xdb$Oid2IntName(s.object_id),
          s.xmldata.schema_url,
          case when bitand(to_number(s.xmldata.flags, 'xxxxxxxx'), 128) = 128
               then 'NONE'
               else case when
                    bitand(to_number(s.xmldata.flags, 'xxxxxxxx'), 64) = 64
                    then  'RESMETADATA'
                    else  'CONTENTS'
                    end
          end,
          case when bitand(to_number(s.xmldata.flags,'xxxxxxxx'), 16384) = 16384
              then 'YES' else 'NO' end,
          s.sys_nc_oid$,
          case when bitand(to_number(s.xmldata.flags,'xxxxxxxx'), 32768) = 32768
              then 'YES' else 'NO' end
    from xdb.xdb$schema s
    where bitand(to_number(s.xmldata.flags,'xxxxxxxx'), 16) = 16
    and s.xmldata.schema_url
       not in (select s2.xmldata.schema_url
               from xdb.xdb$schema s2, user$ u2
               where u2.user# = userenv('SCHEMAID')
               and   u2.name  = s.xmldata.schema_owner)

View Text - HTML Formatted

SELECT U.NAME
, S.XMLDATA.SCHEMA_URL
, CASE WHEN BITAND(TO_NUMBER(S.XMLDATA.FLAGS
, 'XXXXXXXX')
, 16) = 16 THEN 'NO' ELSE 'YES' END
, CASE WHEN BITAND(TO_NUMBER(S.XMLDATA.FLAGS
, 'XXXXXXXX')
, 16384) = 16384 THEN XDB.DBMS_CSX_INT.GETCSXSCHEMA(XMLTYPE(VALUE(S).GETCLOBVAL())) ELSE VALUE(S) END
, XDB.DBMS_XMLSCHEMA_INT.XDB$OID2INTNAME(S.OBJECT_ID)
, CASE WHEN BITAND(TO_NUMBER(S.XMLDATA.FLAGS
, 'XXXXXXXX')
, 16) = 16 THEN S.XMLDATA.SCHEMA_URL ELSE 'HTTP://XMLNS.ORACLE.COM/XDB/SCHEMAS/' || S.XMLDATA.SCHEMA_OWNER || '/' || CASE WHEN SUBSTR(S.XMLDATA.SCHEMA_URL
, 1
, 7) = 'HTTP://' THEN SUBSTR(S.XMLDATA.SCHEMA_URL
, 8) ELSE S.XMLDATA.SCHEMA_URL END END
, CASE WHEN BITAND(TO_NUMBER(S.XMLDATA.FLAGS
, 'XXXXXXXX')
, 128) = 128 THEN 'NONE' ELSE CASE WHEN BITAND(TO_NUMBER(S.XMLDATA.FLAGS
, 'XXXXXXXX')
, 64) = 64 THEN 'RESMETADATA' ELSE 'CONTENTS' END END
, CASE WHEN BITAND(TO_NUMBER(S.XMLDATA.FLAGS
, 'XXXXXXXX')
, 16384) = 16384 THEN 'YES' ELSE 'NO' END
, S.SYS_NC_OID$
, CASE WHEN BITAND(TO_NUMBER(S.XMLDATA.FLAGS
, 'XXXXXXXX')
, 32768) = 32768 THEN 'YES' ELSE 'NO' END
FROM USER$ U
, XDB.XDB$SCHEMA S
WHERE U.USER# = USERENV('SCHEMAID')
AND U.NAME = S.XMLDATA.SCHEMA_OWNER UNION ALL SELECT S.XMLDATA.SCHEMA_OWNER
, S.XMLDATA.SCHEMA_URL
, 'NO'
, VALUE(S)
, XDB.DBMS_XMLSCHEMA_INT.XDB$OID2INTNAME(S.OBJECT_ID)
, S.XMLDATA.SCHEMA_URL
, CASE WHEN BITAND(TO_NUMBER(S.XMLDATA.FLAGS
, 'XXXXXXXX')
, 128) = 128 THEN 'NONE' ELSE CASE WHEN BITAND(TO_NUMBER(S.XMLDATA.FLAGS
, 'XXXXXXXX')
, 64) = 64 THEN 'RESMETADATA' ELSE 'CONTENTS' END END
, CASE WHEN BITAND(TO_NUMBER(S.XMLDATA.FLAGS
, 'XXXXXXXX')
, 16384) = 16384 THEN 'YES' ELSE 'NO' END
, S.SYS_NC_OID$
, CASE WHEN BITAND(TO_NUMBER(S.XMLDATA.FLAGS
, 'XXXXXXXX')
, 32768) = 32768 THEN 'YES' ELSE 'NO' END
FROM XDB.XDB$SCHEMA S
WHERE BITAND(TO_NUMBER(S.XMLDATA.FLAGS
, 'XXXXXXXX')
, 16) = 16
AND S.XMLDATA.SCHEMA_URL NOT IN (SELECT S2.XMLDATA.SCHEMA_URL
FROM XDB.XDB$SCHEMA S2
, USER$ U2
WHERE U2.USER# = USERENV('SCHEMAID')
AND U2.NAME = S.XMLDATA.SCHEMA_OWNER)