Re: XMLTYPE NESTED TABLE

ok thanks...

in this case this error is normal (bad usage)... I've no error in schema definition (or xdb annotation) or create query ?

On 1 juil, 12:03, "Vladimir M. Zakharychev" <vladimir.zakharyc..._at_gmail.com> wrote:
> 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:47:41 CDT