Re: XMLTYPE NESTED TABLE

On Jul 1, 1:29 pm, jacquesh <jhu..._at_gmail.com> wrote:
> Hello,
>
> I've a problem with this scenario ...
>
> My Schema:
> <xs:schema xmlns:xs="http://www.w3.org/2001/XMLSchema"
> xmlns:xdb="http://xmlns.oracle.com/xdb"
> version="1" xdb:storeVarrayAsTable="true">
> <!-- racine du document -->
> <xs:element name="FIELDS" xdb:defaultTable="ACLFIELDS">
> <xs:complexType xdb:SQLType="ACLFIELDS_T">
> <xs:sequence>
> <xs:element ref="XMLNAME" minOccurs="1"
> maxOccurs="unbounded"/>
> </xs:sequence>
> </xs:complexType>
> </xs:element>
> <xs:element name="XMLNAME" xdb:SQLCollType="XMLNAME_V"
> xdb:SQLName="XMLNAME_VARRAY">
> <xs:complexType xdb:SQLType="XMLNAME_T">
> <xs:sequence>
> <xs:element ref="PROFILE" maxOccurs="1" minOccurs="1"/
>
> </xs:sequence>
> <xs:attribute name="ID" use="required" type="xs:string"
> xdb:SQLName="FIELD_NAME"/>
> <xs:attribute name="TYPE" use="required"
> type="type_control"
> xdb:SQLName="FIELD_TYPE"/>
> </xs:complexType>
> </xs:element>
> <xs:element name="PROFILE" xdb:SQLName="PROFILE">
> <xs:complexType xdb:SQLType="PROFILE_T">
> <xs:sequence maxOccurs="1" minOccurs="1">
> <xs:element ref="AU" maxOccurs="1" minOccurs="1"/>
> <xs:element ref="CM" maxOccurs="1" minOccurs="1"/>
> <xs:element ref="CT" maxOccurs="1" minOccurs="1"/>
> <xs:element ref="EC" maxOccurs="1" minOccurs="1"/>
> <xs:element ref="MA" maxOccurs="1" minOccurs="1"/>
> <xs:element ref="OT" maxOccurs="1" minOccurs="1"/>
> </xs:sequence>
> </xs:complexType>
> </xs:element>
> <xs:element name="OT" type="type_access" xdb:SQLName="OT"/>
> <xs:element name="MA" type="type_access" xdb:SQLName="MA"/>
> <xs:element name="EC" type="type_access" xdb:SQLName="EC"/>
> <xs:element name="CT" type="type_access" xdb:SQLName="CT"/>
> <xs:element name="CM" type="type_access" xdb:SQLName="CM"/>
> <xs:element name="AU" type="type_access" xdb:SQLName="AU"/>
> <!-- def. des types -->
> <xs:simpleType name="type_access">
> <xs:restriction base="xs:string">
> <xs:enumeration value="V"/>
> <xs:enumeration value="W"/>
> <xs:enumeration value="H"/>
> </xs:restriction>
> </xs:simpleType>
> <xs:simpleType name="type_control">
> <xs:restriction base="xs:string">
> <xs:enumeration value="LBL"/>
> <xs:enumeration value="CHK"/>
> <xs:enumeration value="RAD"/>
> <xs:enumeration value="LST"/>
> <xs:enumeration value="TXT"/>
> </xs:restriction>
> </xs:simpleType>
> </xs:schema>
>
> BEGIN
> DBMS_XMLSCHEMA.REGISTERSCHEMA(
> schemaurl => 'http://xxx/projet/yyy/schema/aclfields.xsd',
> schemadoc => sys.UriFactory.getUri('http://xxx:xxx@localhost:
> 8082/home/yyy/xsd/fields.xsd'),
> local => TRUE,
> gentypes => TRUE,
> genbean => FALSE,
> gentables => TRUE,
> force => FALSE,
> owner => 'D47');
> END;
> /
>
> commit ;
>
> -- Table
> DROP TABLE FIELDS ;
> CREATE TABLE FIELDS (
> ID NUMBER,
> NOM VARCHAR2(25 CHAR),
> ACL_DEF XMLTYPE,
> COD_HOP VARCHAR2(5 CHAR))
> XMLTYPE COLUMN ACL_DEF
> XMLSCHEMA "http://xxx/projet/yyy/schema/aclfields.xsd"
> ELEMENT "FIELDS"
> VARRAY ACL_DEF."XMLDATA"."XMLNAME"
> STORE AS TABLE "XMLNAME_TABLE"
> ((PRIMARY KEY (NESTED_TABLE_ID, SYS_NC_ARRAY_INDEX$))
> ORGANIZATION INDEX OVERFLOW)
> /
>
> Table creee.
>
> All is ok... but when i do
>
> SQL> desc XMLNAME_TABLE
> Nom NULL ? Type
> ----------------------------------------- --------
> ----------------------------
> SYS_XDBPD$ XDB.XDB$RAW_LIST_T
> FIELD_NAME VARCHAR2(4000
> CHAR)
> FIELD_TYPE XDB.XDB$ENUM_T
> PROFILE PROFILE_T
>
> SQL> select * from XMLNAME_TABLE ;
> select * from XMLNAME_TABLE
> *
> ERREUR a la ligne 1 :
> ORA-22812: impossible de referencer la table de stockage de la colonne
> de table
> imbriquee
>
> why ?? any idea..
>
> thanks

You can't select from nested tables directly, unless you specify a special hint that allows them to be dereferenced (the hint is undocumented and is primarily used by the export utility.) Documented way to access nested tables as normal tables is with the table collection expression:

select f.id, n.* from fields f, TABLE(f.acl_def.xmldata.xmlname) n

This is called collection unnesting, look it up in the documentation for more details and examples.

Hth,

   Vladimir M. Zakharychev
   N-Networks, makers of Dynamic PSP(tm)    http://www.dynamicpsp.com Received on Sun Jul 01 2007 - 05:03:04 CDT