VIEW: SYS.USER_XML_TABLES

Source

View Text - Preformatted

select o.name, null, null, null,
    case when bitand(opq.flags,69) = 1 then 'OBJECT-RELATIONAL'
         when bitand(opq.flags,69) = 68 then 'BINARY'
    else 'CLOB' end,
    case when bitand(opq.flags,69) = 68 then
        case when bitand(opq.flags,128) = 128 then 'YES' else 'NO' end
    else NULL end,
    case when bitand(opq.flags,69) = 68  then
        case when bitand(opq.flags,256) = 256 then 'NO' else 'YES' end
    else NULL end
from sys.opqtype$ opq, sys.tab$ t, sys.obj$ o, sys.coltype$ ac, sys.col$ tc
where o.owner# = userenv('SCHEMAID')
  and o.obj# = t.obj#
  and bitand(t.property, 1) = 1
  and t.obj# = tc.obj#
  and tc.name = 'SYS_NC_ROWINFO$'
  and tc.obj# = ac.obj#
  and tc.intcol# = ac.intcol#
  and ac.toid = '00000000000000000000000000020100'
  and tc.intcol# =  opq.intcol#
  and tc.obj# =  opq.obj#
  and bitand(opq.flags,2) = 0
 union all
  select o.name, schm.xmldata.schema_url, schm.xmldata.schema_owner,
decode(xel.xmldata.property.name, null,
        xel.xmldata.property.propref_name.name, xel.xmldata.property.name),
  case when bitand(opq.flags,69) = 1 then 'OBJECT-RELATIONAL'
       when bitand(opq.flags,69) = 68 then 'BINARY'
  else 'CLOB' end,
  case when bitand(opq.flags,69) = 68 then
      case when bitand(opq.flags,128) = 128 then 'YES' else 'NO' end
  else NULL end,
  case when bitand(opq.flags,69) = 68  then
      case when bitand(opq.flags,256) = 256 then 'NO' else 'YES' end
  else NULL end
from xdb.xdb$element xel, xdb.xdb$schema schm, sys.opqtype$ opq,
      sys.tab$ t, sys.obj$ o, sys.coltype$ ac, sys.col$ tc
where o.owner# = userenv('SCHEMAID')
  and o.obj# = t.obj#
  and bitand(t.property, 1) = 1
  and t.obj# = tc.obj#
  and tc.name = 'SYS_NC_ROWINFO$'
  and tc.obj# = ac.obj#
  and tc.intcol# = ac.intcol#
  and ac.toid = '00000000000000000000000000020100'
  and tc.intcol# =  opq.intcol#
  and tc.obj# =  opq.obj#
  and opq.schemaoid =  schm.sys_nc_oid$
  and bitand(opq.flags,2) = 2
  and opq.elemnum =  xel.xmldata.property.prop_number

View Text - HTML Formatted

SELECT O.NAME
, NULL
, NULL
, NULL
, CASE WHEN BITAND(OPQ.FLAGS
, 69) = 1 THEN 'OBJECT-RELATIONAL' WHEN BITAND(OPQ.FLAGS
, 69) = 68 THEN 'BINARY' ELSE 'CLOB' END
, CASE WHEN BITAND(OPQ.FLAGS
, 69) = 68 THEN CASE WHEN BITAND(OPQ.FLAGS
, 128) = 128 THEN 'YES' ELSE 'NO' END ELSE NULL END
, CASE WHEN BITAND(OPQ.FLAGS
, 69) = 68 THEN CASE WHEN BITAND(OPQ.FLAGS
, 256) = 256 THEN 'NO' ELSE 'YES' END ELSE NULL END FROM SYS.OPQTYPE$ OPQ
, SYS.TAB$ T
, SYS.OBJ$ O
, SYS.COLTYPE$ AC
, SYS.COL$ TC WHERE O.OWNER# = USERENV('SCHEMAID')
AND O.OBJ# = T.OBJ#
AND BITAND(T.PROPERTY
, 1) = 1
AND T.OBJ# = TC.OBJ#
AND TC.NAME = 'SYS_NC_ROWINFO$'
AND TC.OBJ# = AC.OBJ#
AND TC.INTCOL# = AC.INTCOL#
AND AC.TOID = '00000000000000000000000000020100'
AND TC.INTCOL# = OPQ.INTCOL#
AND TC.OBJ# = OPQ.OBJ#
AND BITAND(OPQ.FLAGS
, 2) = 0 UNION ALL SELECT O.NAME
, SCHM.XMLDATA.SCHEMA_URL
, SCHM.XMLDATA.SCHEMA_OWNER
, DECODE(XEL.XMLDATA.PROPERTY.NAME
, NULL
, XEL.XMLDATA.PROPERTY.PROPREF_NAME.NAME
, XEL.XMLDATA.PROPERTY.NAME)
, CASE WHEN BITAND(OPQ.FLAGS
, 69) = 1 THEN 'OBJECT-RELATIONAL' WHEN BITAND(OPQ.FLAGS
, 69) = 68 THEN 'BINARY' ELSE 'CLOB' END
, CASE WHEN BITAND(OPQ.FLAGS
, 69) = 68 THEN CASE WHEN BITAND(OPQ.FLAGS
, 128) = 128 THEN 'YES' ELSE 'NO' END ELSE NULL END
, CASE WHEN BITAND(OPQ.FLAGS
, 69) = 68 THEN CASE WHEN BITAND(OPQ.FLAGS
, 256) = 256 THEN 'NO' ELSE 'YES' END ELSE NULL END FROM XDB.XDB$ELEMENT XEL
, XDB.XDB$SCHEMA SCHM
, SYS.OPQTYPE$ OPQ
, SYS.TAB$ T
, SYS.OBJ$ O
, SYS.COLTYPE$ AC
, SYS.COL$ TC WHERE O.OWNER# = USERENV('SCHEMAID')
AND O.OBJ# = T.OBJ#
AND BITAND(T.PROPERTY
, 1) = 1
AND T.OBJ# = TC.OBJ#
AND TC.NAME = 'SYS_NC_ROWINFO$'
AND TC.OBJ# = AC.OBJ#
AND TC.INTCOL# = AC.INTCOL#
AND AC.TOID = '00000000000000000000000000020100'
AND TC.INTCOL# = OPQ.INTCOL#
AND TC.OBJ# = OPQ.OBJ#
AND OPQ.SCHEMAOID = SCHM.SYS_NC_OID$
AND BITAND(OPQ.FLAGS
, 2) = 2
AND OPQ.ELEMNUM = XEL.XMLDATA.PROPERTY.PROP_NUMBER