VIEW: SYS.DBA_XML_SCHEMA_ATTRIBUTES

Source

View Text - Preformatted

select s.xmldata.schema_owner OWNER,
       s.xmldata.schema_url SCHEMA_URL,
       s.xmldata.target_namespace TARGET_NAMESPACE,
       (case
          when a.xmldata.name IS NULL
          then
             a.xmldata.propref_name.name
          else
             a.xmldata.name
          end
       )AS ATTRIBUTE_NAME,
       (case
          when a.xmldata.name IS NULL
          then
             1
          else
             0
          end
       )AS IS_REF,
       (case
          when a.xmldata.typename.name IS NULL
          then
              (select a1.xmldata.typename.name from xdb.xdb$attribute a1 where ref(a1)=a.xmldata.propref_ref)
          else
             a.xmldata.typename.name
          end
       )AS TYPE_NAME,
       a.xmldata.global GLOBAL,
       value(a) ATTRIBUTE,
       hextoraw(e.xmldata.property.prop_number) AS ELEMENT_ID,
       a.xmldata.sqltype AS SQL_TYPE,
       a.xmldata.sqlname AS SQL_NAME
  from xdb.xdb$schema s, xdb.xdb$attribute a,
       xdb.xdb$element e, xdb.xdb$complex_type ct,
       table(ct.xmldata.attributes) att
 where  sys_op_r2o(a.xmldata.parent_schema) = s.sys_nc_oid$ and
       ref(ct) = e.xmldata.property.type_ref and
       att.column_value = ref(a)
UNION ALL
select s.xmldata.schema_owner OWNER,
       s.xmldata.schema_url SCHEMA_URL,
       s.xmldata.target_namespace TARGET_NAMESPACE,
       (case
          when a.xmldata.name IS NULL
          then
             a.xmldata.propref_name.name
          else
             a.xmldata.name
          end
       )AS ATTRIBUTE_NAME,
       (case
          when a.xmldata.name IS NULL
          then
             1
          else
             0
          end
       )AS IS_REF,
       (case
          when a.xmldata.typename.name IS NULL
          then
              (select a1.xmldata.typename.name from xdb.xdb$attribute a1 where ref(a1)=a.xmldata.propref_ref)
          else
             a.xmldata.typename.name
          end
       )AS TYPE_NAME,
       a.xmldata.global GLOBAL,
       value(a) ATTRIBUTE,
       hextoraw(e.xmldata.property.prop_number) AS ELEMENT_ID,
       a.xmldata.sqltype AS SQL_TYPE,
       a.xmldata.sqlname AS SQL_NAME
  from xdb.xdb$schema s, xdb.xdb$attribute a,
       xdb.xdb$element e, xdb.xdb$complex_type ct,
       table(ct.xmldata.simplecont.restriction.attributes) att
 where  sys_op_r2o(a.xmldata.parent_schema) = s.sys_nc_oid$ and
       ref(ct) = e.xmldata.property.type_ref and
       att.column_value = ref(a)
UNION ALL
select s.xmldata.schema_owner OWNER,
       s.xmldata.schema_url SCHEMA_URL,
       s.xmldata.target_namespace TARGET_NAMESPACE,
       (case
          when a.xmldata.name IS NULL
          then
             a.xmldata.propref_name.name
          else
             a.xmldata.name
          end
       )AS ATTRIBUTE_NAME,
       (case
          when a.xmldata.name IS NULL
          then
             1
          else
             0
          end
       )AS IS_REF,
       (case
          when a.xmldata.typename.name IS NULL
          then
              (select a1.xmldata.typename.name from xdb.xdb$attribute a1 where ref(a1)=a.xmldata.propref_ref)
          else
             a.xmldata.typename.name
          end
       )AS TYPE_NAME,
       a.xmldata.global GLOBAL,
       value(a) ATTRIBUTE,
       hextoraw(e.xmldata.property.prop_number) AS ELEMENT_ID,
       a.xmldata.sqltype AS SQL_TYPE,
       a.xmldata.sqlname AS SQL_NAME
  from xdb.xdb$schema s, xdb.xdb$attribute a,
       xdb.xdb$element e, xdb.xdb$complex_type ct,
       table(ct.xmldata.simplecont.extension.attributes) att
 where  sys_op_r2o(a.xmldata.parent_schema) = s.sys_nc_oid$ and
       ref(ct) = e.xmldata.property.type_ref and
       att.column_value = ref(a)
UNION ALL
select s.xmldata.schema_owner OWNER,
       s.xmldata.schema_url SCHEMA_URL,
       s.xmldata.target_namespace TARGET_NAMESPACE,
       (case
          when a.xmldata.name IS NULL
          then
             a.xmldata.propref_name.name
          else
             a.xmldata.name
          end
       )AS ATTRIBUTE_NAME,
       (case
          when a.xmldata.name IS NULL
          then
             1
          else
             0
          end
       )AS IS_REF,
       (case
          when a.xmldata.typename.name IS NULL
          then
             ( select a1.xmldata.typename.name from xdb.xdb$attribute a1 where ref(a1)=a.xmldata.propref_ref)
          else
             a.xmldata.typename.name
          end
       )AS TYPE_NAME,
       a.xmldata.global GLOBAL,
       value(a) ATTRIBUTE,
       NULL AS ELEMENT_ID,
       a.xmldata.sqltype AS SQL_TYPE,
       a.xmldata.sqlname AS SQL_NAME
   from xdb.xdb$schema s, xdb.xdb$attribute a
 where  sys_op_r2o(a.xmldata.parent_schema) = s.sys_nc_oid$ and
       (
         (
	   ref(a) NOT IN (select t.column_value from xdb.xdb$complex_type ct,table(ct.xmldata.attributes)t)
           AND
           ref(a) NOT IN (select t.column_value from xdb.xdb$complex_type ct,table(ct.xmldata.simplecont.extension.attributes)t)
           AND
           ref(a) NOT IN (select t.column_value from xdb.xdb$complex_type ct,table(ct.xmldata.simplecont.restriction.attributes)t)
	 )
         OR
         (
           (
             ref(a)  IN (select t.column_value from xdb.xdb$complex_type ct,table(ct.xmldata.attributes)t)
             OR
             ref(a) IN (select t.column_value from xdb.xdb$complex_type ct,table(ct.xmldata.simplecont.extension.attributes)t)
             OR
             ref(a) IN (select t.column_value from xdb.xdb$complex_type ct,table(ct.xmldata.simplecont.restriction.attributes)t)
           )
           and (select ref(ct) from xdb.xdb$complex_type ct,table(ct.xmldata.attributes)t,table(ct.xmldata.simplecont.extension.attributes)t1,table(ct.xmldata.simplecont.restriction.attributes)t2 where
	     ref(a)  = t.column_value
             OR
             ref(a) = t1.column_value
             OR
             ref(a) = t2.column_value
	   ) NOT IN ( select e.xmldata.property.type_ref from xdb.xdb$element e)
         )
       )

View Text - HTML Formatted

SELECT S.XMLDATA.SCHEMA_OWNER OWNER
, S.XMLDATA.SCHEMA_URL SCHEMA_URL
, S.XMLDATA.TARGET_NAMESPACE TARGET_NAMESPACE
, (CASE WHEN A.XMLDATA.NAME IS NULL THEN A.XMLDATA.PROPREF_NAME.NAME ELSE A.XMLDATA.NAME END )AS ATTRIBUTE_NAME
, (CASE WHEN A.XMLDATA.NAME IS NULL THEN 1 ELSE 0 END )AS IS_REF
, (CASE WHEN A.XMLDATA.TYPENAME.NAME IS NULL THEN (SELECT A1.XMLDATA.TYPENAME.NAME
FROM XDB.XDB$ATTRIBUTE A1
WHERE REF(A1)=A.XMLDATA.PROPREF_REF) ELSE A.XMLDATA.TYPENAME.NAME END )AS TYPE_NAME
, A.XMLDATA.GLOBAL GLOBAL
, VALUE(A) ATTRIBUTE
, HEXTORAW(E.XMLDATA.PROPERTY.PROP_NUMBER) AS ELEMENT_ID
, A.XMLDATA.SQLTYPE AS SQL_TYPE
, A.XMLDATA.SQLNAME AS SQL_NAME
FROM XDB.XDB$SCHEMA S
, XDB.XDB$ATTRIBUTE A
, XDB.XDB$ELEMENT E
, XDB.XDB$COMPLEX_TYPE CT
, TABLE(CT.XMLDATA.ATTRIBUTES) ATT
WHERE SYS_OP_R2O(A.XMLDATA.PARENT_SCHEMA) = S.SYS_NC_OID$ AND REF(CT) = E.XMLDATA.PROPERTY.TYPE_REF AND ATT.COLUMN_VALUE = REF(A) UNION ALL SELECT S.XMLDATA.SCHEMA_OWNER OWNER
, S.XMLDATA.SCHEMA_URL SCHEMA_URL
, S.XMLDATA.TARGET_NAMESPACE TARGET_NAMESPACE
, (CASE WHEN A.XMLDATA.NAME IS NULL THEN A.XMLDATA.PROPREF_NAME.NAME ELSE A.XMLDATA.NAME END )AS ATTRIBUTE_NAME
, (CASE WHEN A.XMLDATA.NAME IS NULL THEN 1 ELSE 0 END )AS IS_REF
, (CASE WHEN A.XMLDATA.TYPENAME.NAME IS NULL THEN (SELECT A1.XMLDATA.TYPENAME.NAME
FROM XDB.XDB$ATTRIBUTE A1
WHERE REF(A1)=A.XMLDATA.PROPREF_REF) ELSE A.XMLDATA.TYPENAME.NAME END )AS TYPE_NAME
, A.XMLDATA.GLOBAL GLOBAL
, VALUE(A) ATTRIBUTE
, HEXTORAW(E.XMLDATA.PROPERTY.PROP_NUMBER) AS ELEMENT_ID
, A.XMLDATA.SQLTYPE AS SQL_TYPE
, A.XMLDATA.SQLNAME AS SQL_NAME
FROM XDB.XDB$SCHEMA S
, XDB.XDB$ATTRIBUTE A
, XDB.XDB$ELEMENT E
, XDB.XDB$COMPLEX_TYPE CT
, TABLE(CT.XMLDATA.SIMPLECONT.RESTRICTION.ATTRIBUTES) ATT
WHERE SYS_OP_R2O(A.XMLDATA.PARENT_SCHEMA) = S.SYS_NC_OID$ AND REF(CT) = E.XMLDATA.PROPERTY.TYPE_REF AND ATT.COLUMN_VALUE = REF(A) UNION ALL SELECT S.XMLDATA.SCHEMA_OWNER OWNER
, S.XMLDATA.SCHEMA_URL SCHEMA_URL
, S.XMLDATA.TARGET_NAMESPACE TARGET_NAMESPACE
, (CASE WHEN A.XMLDATA.NAME IS NULL THEN A.XMLDATA.PROPREF_NAME.NAME ELSE A.XMLDATA.NAME END )AS ATTRIBUTE_NAME
, (CASE WHEN A.XMLDATA.NAME IS NULL THEN 1 ELSE 0 END )AS IS_REF
, (CASE WHEN A.XMLDATA.TYPENAME.NAME IS NULL THEN (SELECT A1.XMLDATA.TYPENAME.NAME
FROM XDB.XDB$ATTRIBUTE A1
WHERE REF(A1)=A.XMLDATA.PROPREF_REF) ELSE A.XMLDATA.TYPENAME.NAME END )AS TYPE_NAME
, A.XMLDATA.GLOBAL GLOBAL
, VALUE(A) ATTRIBUTE
, HEXTORAW(E.XMLDATA.PROPERTY.PROP_NUMBER) AS ELEMENT_ID
, A.XMLDATA.SQLTYPE AS SQL_TYPE
, A.XMLDATA.SQLNAME AS SQL_NAME
FROM XDB.XDB$SCHEMA S
, XDB.XDB$ATTRIBUTE A
, XDB.XDB$ELEMENT E
, XDB.XDB$COMPLEX_TYPE CT
, TABLE(CT.XMLDATA.SIMPLECONT.EXTENSION.ATTRIBUTES) ATT
WHERE SYS_OP_R2O(A.XMLDATA.PARENT_SCHEMA) = S.SYS_NC_OID$ AND REF(CT) = E.XMLDATA.PROPERTY.TYPE_REF AND ATT.COLUMN_VALUE = REF(A) UNION ALL SELECT S.XMLDATA.SCHEMA_OWNER OWNER
, S.XMLDATA.SCHEMA_URL SCHEMA_URL
, S.XMLDATA.TARGET_NAMESPACE TARGET_NAMESPACE
, (CASE WHEN A.XMLDATA.NAME IS NULL THEN A.XMLDATA.PROPREF_NAME.NAME ELSE A.XMLDATA.NAME END )AS ATTRIBUTE_NAME
, (CASE WHEN A.XMLDATA.NAME IS NULL THEN 1 ELSE 0 END )AS IS_REF
, (CASE WHEN A.XMLDATA.TYPENAME.NAME IS NULL THEN ( SELECT A1.XMLDATA.TYPENAME.NAME
FROM XDB.XDB$ATTRIBUTE A1
WHERE REF(A1)=A.XMLDATA.PROPREF_REF) ELSE A.XMLDATA.TYPENAME.NAME END )AS TYPE_NAME
, A.XMLDATA.GLOBAL GLOBAL
, VALUE(A) ATTRIBUTE
, NULL AS ELEMENT_ID
, A.XMLDATA.SQLTYPE AS SQL_TYPE
, A.XMLDATA.SQLNAME AS SQL_NAME
FROM XDB.XDB$SCHEMA S
, XDB.XDB$ATTRIBUTE A
WHERE SYS_OP_R2O(A.XMLDATA.PARENT_SCHEMA) = S.SYS_NC_OID$ AND ( ( REF(A) NOT IN (SELECT T.COLUMN_VALUE
FROM XDB.XDB$COMPLEX_TYPE CT
, TABLE(CT.XMLDATA.ATTRIBUTES)T) AND REF(A) NOT IN (SELECT T.COLUMN_VALUE
FROM XDB.XDB$COMPLEX_TYPE CT
, TABLE(CT.XMLDATA.SIMPLECONT.EXTENSION.ATTRIBUTES)T) AND REF(A) NOT IN (SELECT T.COLUMN_VALUE
FROM XDB.XDB$COMPLEX_TYPE CT
, TABLE(CT.XMLDATA.SIMPLECONT.RESTRICTION.ATTRIBUTES)T) ) OR ( ( REF(A) IN (SELECT T.COLUMN_VALUE
FROM XDB.XDB$COMPLEX_TYPE CT
, TABLE(CT.XMLDATA.ATTRIBUTES)T) OR REF(A) IN (SELECT T.COLUMN_VALUE
FROM XDB.XDB$COMPLEX_TYPE CT
, TABLE(CT.XMLDATA.SIMPLECONT.EXTENSION.ATTRIBUTES)T) OR REF(A) IN (SELECT T.COLUMN_VALUE
FROM XDB.XDB$COMPLEX_TYPE CT
, TABLE(CT.XMLDATA.SIMPLECONT.RESTRICTION.ATTRIBUTES)T) )
AND (SELECT REF(CT)
FROM XDB.XDB$COMPLEX_TYPE CT
, TABLE(CT.XMLDATA.ATTRIBUTES)T
, TABLE(CT.XMLDATA.SIMPLECONT.EXTENSION.ATTRIBUTES)T1
, TABLE(CT.XMLDATA.SIMPLECONT.RESTRICTION.ATTRIBUTES)T2 WHERE REF(A) = T.COLUMN_VALUE OR REF(A) = T1.COLUMN_VALUE OR REF(A) = T2.COLUMN_VALUE ) NOT IN ( SELECT E.XMLDATA.PROPERTY.TYPE_REF
FROM XDB.XDB$ELEMENT E) ) )