Re: XMLTYPE NESTED TABLE

On Jul 1, 2:47 pm, jacquesh <jhu..._at_gmail.com> wrote:
> 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
>
> ok thanks...
>
> in this case this error is normal (bad usage)...
> I've no error in schema definition (or xdb annotation) or create
> query ?
>

Yes, this error is expected and normal. It's just the way it works.

Regards,

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