1 2 Previous Next 15 Replies Latest reply: May 14, 2013 8:13 PM by Marco Gralike RSS

    Partial XMLType toObject

    1008638
      Hi,

      I have an XML schema registered with Oracle. The schema is annotated and during registration the PL/SQL types are created. I can validate an XMLType with this schema, as well as use toObject to populate an object with data from the XMLType. The XML looks like this:

      <root>
      <complexObject>...... very complex object with many elements, sub-elements and sequences .......</complexObject>
      <complexObject>...... very complex object with many elements, sub-elements and sequences .......</complexObject>
      <complexObject>...... very complex object with many elements, sub-elements and sequences .......</complexObject>
      <complexObject>...... very complex object with many elements, sub-elements and sequences .......</complexObject>
      <complexObject>...... very complex object with many elements, sub-elements and sequences .......</complexObject>
      </root>

      There can be a lot of "complexObject" elements in the sequence and the XML can be huge.
      The <root> element has the corresponding T_ROOT type, the <complexObject> elements had the corresponding T_COMPLEXOBJECT type. T_ROOT has a VARRAY of T_COMPLEXOBJECT.

      So, like I said before, I can populate an object of type T_ROOT from the XML without an issue using toObject on the XMLType. However, due to the complexity and size of the XML, I want to split the big XML into multiple T_COMPLEXOBJECT objects. I can do this using XPath extract in a query, returning me one XMLType row per "complexObject". Here is where I'm stuck: I want to use a cursor on the above query and process each "complexObject" one by one. For this, I need to somehow be able to do something similar with toObject, but on the "complexObject" XMLType fragment only to populate an object of type T_COMPLEXOBJECT, not on the whole T_ROOT.

      If I do:

      l_xml.toObject(l_obj, 'myschema.xsd', 'complexObject');

      instead of

      l_xml.toObject(l_obj_root, 'myschema.xsd', 'root');

      I get:

      ORA-31043: Element 'complexObject' not globally defined in schema 'myschema.xsd'
      ORA-06512: at "SYS.XMLTYPE", line 196
      ORA-06512: at line 38

      Thanks!

      Edited by: 1005635 on May 13, 2013 10:40 AM
        • 1. Re: Partial XMLType toObject
          KnightOfBlueArmor
          Based on the error message, it sounds like Oracle is telling you that the schema definition doesn't allow complexType to appear as a root of the document; perhaps the xs:element for complexType is nested in another complexType, rather than standing on its own. Can you show us a bit of your schema definition?
          • 2. Re: Partial XMLType toObject
            1008638
            Here's a simplification of the schema:

            <?xml version="1.0" encoding="UTF-8"?>
            <xs:schema
            xmlns:xs="http://www.w3.org/2001/XMLSchema"
            xmlns:xdb="http://xmlns.oracle.com/xdb"
            xmlns:MYS="http://www.myschema.net/schema/MYS/1"
            targetNamespace="http://www.myschema.net/schema/MYS/1"
            elementFormDefault="qualified"
            attributeFormDefault="unqualified"
            version="1.0"
            xdb:storeVarrayAsTable="true"
            >
            ..... all other complex types .....
            <xs:complexType name="complexObject-type" xdb:SQLType="T_COMPLEXOBJECT" xdb:maintainDOM="false">
            <xs:sequence>
            ... all the other elements ....
            </xs:sequence>
            </xs:complexType>
            <xs:complexType name="root-type" xdb:SQLType="T_ROOT" xdb:maintainDOM="false">
            <xs:sequence>
            <xs:element name="root" type="MYS:complexObject-type" maxOccurs="unbounded" minOccurs="1" xdb:SQLCollType="T_COMPLOBJ_COLL" xdb:SQLName="COMPLEX_OBJECT"></xs:element>
            </xs:sequence>
            </xs:complexType>
            <xs:element name="root" type="MYS:root-type" xdb:SQLName="ROOTS"></xs:element>
            </xs:schema>

            And you are correct, "complexObject" is not the root of the document and cannot be the root, it is part of the root sequence which I want to parse separately into a T_COMPLEXOBJECT one by one.

            Edited by: 1005635 on May 13, 2013 10:58 AM
            • 3. Re: Partial XMLType toObject
              mdrake-Oracle
              The use of toObject in conjuction with types generated by the XML Schema is not supported. If you want to generate Objects from XML you must construct them using XPath Queries.

              If you provide the complete set of XML schemas I can give you some pointers.
              • 4. Re: Partial XMLType toObject
                1008638
                Unfortunately I cannot share the complete XML schemas. I was trying to avoid writing all the XPath queries to populate the objects and auto populate them using toObject.

                However, since this works for the root element only, I think I might have thought of a workaround to make this work :)
                If the following XML is conforming to the schema:

                <root>
                <complexObject>...... very complex object with many elements, sub-elements and sequences .......</complexObject>
                <complexObject>...... very complex object with many elements, sub-elements and sequences .......</complexObject>
                <complexObject>...... very complex object with many elements, sub-elements and sequences .......</complexObject>
                <complexObject>...... very complex object with many elements, sub-elements and sequences .......</complexObject>
                <complexObject>...... very complex object with many elements, sub-elements and sequences .......</complexObject>
                </root>

                So is this one:

                <root>
                <complexObject>...... very complex object with many elements, sub-elements and sequences .......</complexObject>
                </root>

                This means I can extract each <complexObject> using XPath one by one, add it inside the <root> element, build an XMLType with everything and apply toObject on it, giving me always a one element collection instead of thousands... process and repeat. This way it will save a lot of memory during processing.
                • 5. Re: Partial XMLType toObject
                  mdrake-Oracle
                  If you are still intending make any direct reference to any of the OBJECT_TYPES that were created by the XML Schema registration process you are in an unsupported (and unsupportable) configuration. We reserve the right to change the entire mechanism used to generate OBJECT_TYPES from XML SCHEMAS even in a patch, with absolutely no warning and absolutely no guarantee of backwards or forwards compatibility.

                  Edited by: mdrake on May 13, 2013 11:25 AM
                  • 6. Re: Partial XMLType toObject
                    1008638
                    Thanks for the warning. Any reasons why this is unsupported? If it is unsupported and can change at any time, why is it provided at all?
                    Does this mean that the entire toObject method is unsupported, since it is dependent on the generated objects?

                    Edited by: 1005635 on May 13, 2013 11:30 AM
                    • 7. Re: Partial XMLType toObject
                      mdrake-Oracle
                      Primarily so we are free to optimize the XML Schema object-model to SQL Object model mapping algorithms as we see fit without having our hands tied by backwards and forwards compatibility. XMLType is meant to be an abstraction over a number of different storage models, so code accessing XML should not be dependent on the underlying infrastructure which should be considered plumbing..

                      If you really want to do this, the correct way to do this is register the schema with the trace event that dumps the set of objects we create (at a particular point in time). Use this code to make you own set of objects.

                      The when you want to populate your objects you can write a select that uses case multiset operators to populate the objects and collections.. It's actually fairly simple in most cases.
                      • 8. Re: Partial XMLType toObject
                        mdrake-Oracle
                        If you have access to metalink see Doc ID 1504299.1
                        • 9. Re: Partial XMLType toObject
                          1008638
                          This is great information, thank you! I think it should be added to the main documentation instead of the Metalink note only, as a big warning... or maybe it is already there and I did not see it :(

                          I will register the schema without creating the objects then, create them myself from script and populate them as you suggested. Do you have any example URLs showing how to populate the objects? Thanks!
                          • 10. Re: Partial XMLType toObject
                            mdrake-Oracle
                            Here's an example
                            SQL> --
                            SQL> -- def XMLDIR = &1
                            SQL> --
                            SQL> def USER_TABLESPACE = USERS
                            SQL> --
                            SQL> def TEMP_TABLESPACE = TEMP
                            SQL> --
                            SQL> drop user &USERNAME cascade
                              2  /
                            old   1: drop user &USERNAME cascade
                            new   1: drop user XDBTEST cascade
                            
                            User dropped.
                            
                            Elapsed: 00:00:01.31
                            SQL> grant unlimited tablespace, create any directory, drop any directory, connect, resource, alter session, create view to &USERNAME identified by &PASSWORD
                              2  /
                            old   1: grant unlimited tablespace, create any directory, drop any directory, connect, resource, alter session, create view to &USERNAME identified by &PASSWOR
                            D
                            new   1: grant unlimited tablespace, create any directory, drop any directory, connect, resource, alter session, create view to XDBTEST identified by XDBTEST
                            
                            Grant succeeded.
                            
                            Elapsed: 00:00:00.03
                            SQL> alter user &USERNAME default tablespace &USER_TABLESPACE temporary tablespace &TEMP_TABLESPACE
                              2  /
                            old   1: alter user &USERNAME default tablespace &USER_TABLESPACE temporary tablespace &TEMP_TABLESPACE
                            new   1: alter user XDBTEST default tablespace USERS temporary tablespace TEMP
                            
                            User altered.
                            
                            Elapsed: 00:00:00.00
                            SQL> connect &USERNAME/&PASSWORD
                            Connected.
                            SQL> --
                            SQL> -- create or replace directory XMLDIR as '&XMLDIR'
                            SQL> -- /
                            SQL> var SCHEMAURL       varchar2(256)
                            SQL> var XMLSCHEMA       CLOB
                            SQL> var INSTANCE        CLOB;
                            SQL> --
                            SQL> set define off
                            SQL> --
                            SQL> alter session set events='31098 trace name context forever'
                              2  /
                            
                            Session altered.
                            
                            Elapsed: 00:00:00.00
                            SQL>
                            SQL> begin
                              2    :XMLSCHEMA :=
                              3  '<xs:schema xmlns:xs="http://www.w3.org/2001/XMLSchema" version="1.0">
                              4          <xs:element name="PurchaseOrder" type="PurchaseOrderType"/>
                              5          <xs:complexType name="PurchaseOrderType">
                              6                  <xs:sequence>
                              7                          <xs:element name="Reference" type="ReferenceType"/>
                              8                          <xs:element name="Actions" type="ActionsType"/>
                              9                          <xs:element name="Rejection" type="RejectionType" minOccurs="0"/>
                             10                          <xs:element name="Requestor" type="RequestorType"/>
                             11                          <xs:element name="User" type="UserType"/>
                             12                          <xs:element name="CostCenter" type="CostCenterType"/>
                             13                          <xs:element name="ShippingInstructions" type="ShippingInstructionsType"/>
                             14                          <xs:element name="SpecialInstructions" type="SpecialInstructionsType"/>
                             15                          <xs:element name="LineItems" type="LineItemsType"/>
                             16                  </xs:sequence>
                             17                  <xs:attribute name="DateCreated" type="xs:dateTime" use="required"/>
                             18          </xs:complexType>
                             19          <xs:complexType name="LineItemsType">
                             20                  <xs:sequence>
                             21                          <xs:element name="LineItem" type="LineItemType" maxOccurs="unbounded"/>
                             22                  </xs:sequence>
                             23          </xs:complexType>
                             24          <xs:complexType name="LineItemType">
                             25                  <xs:sequence>
                             26                          <xs:element name="Part" type="PartType"/>
                             27                          <xs:element name="Quantity" type="QuantityType"/>
                             28                  </xs:sequence>
                             29                  <xs:attribute name="ItemNumber" type="xs:integer"/>
                             30          </xs:complexType>
                             31          <xs:complexType name="PartType">
                             32                  <xs:simpleContent>
                             33                          <xs:extension base="UPCCodeType">
                             34                                  <xs:attribute name="Description" type="DescriptionType" use="required"/>
                             35                                  <xs:attribute name="UnitPrice" type="MoneyType" use="required"/>
                             36                          </xs:extension>
                             37                  </xs:simpleContent>
                             38          </xs:complexType>
                             39          <xs:simpleType name="ReferenceType">
                             40                  <xs:restriction base="xs:string">
                             41                          <xs:minLength value="18"/>
                             42                          <xs:maxLength value="30"/>
                             43                  </xs:restriction>
                             44          </xs:simpleType>
                             45          <xs:complexType name="ActionsType">
                             46                  <xs:sequence>
                             47                          <xs:element name="Action" maxOccurs="4">
                             48                                  <xs:complexType>
                             49                                          <xs:sequence>
                             50                                                  <xs:element name="User" type="UserType"/>
                             51                                                  <xs:element name="Date" type="DateType" minOccurs="0"/>
                             52                                          </xs:sequence>
                             53                                  </xs:complexType>
                             54                          </xs:element>
                             55                  </xs:sequence>
                             56          </xs:complexType>
                             57          <xs:complexType name="RejectionType">
                             58                  <xs:all>
                             59                          <xs:element name="User" type="UserType" minOccurs="0"/>
                             60                          <xs:element name="Date" type="DateType" minOccurs="0"/>
                             61                          <xs:element name="Comments" type="CommentsType" minOccurs="0"/>
                             62                  </xs:all>
                             63          </xs:complexType>
                             64          <xs:complexType name="ShippingInstructionsType">
                             65                  <xs:sequence>
                             66                          <xs:element name="name" type="NameType" minOccurs="0"/>
                             67                          <xs:element name="address" type="AddressType" minOccurs="0"/>
                             68                          <xs:element name="telephone" type="TelephoneType" minOccurs="0"/>
                             69                  </xs:sequence>
                             70          </xs:complexType>
                             71          <xs:simpleType name="MoneyType">
                             72                  <xs:restriction base="xs:decimal">
                             73                          <xs:fractionDigits value="2"/>
                             74                          <xs:totalDigits value="12"/>
                             75                  </xs:restriction>
                             76          </xs:simpleType>
                             77          <xs:simpleType name="QuantityType">
                             78                  <xs:restriction base="xs:decimal">
                             79                          <xs:fractionDigits value="4"/>
                             80                          <xs:totalDigits value="8"/>
                             81                  </xs:restriction>
                             82          </xs:simpleType>
                             83          <xs:simpleType name="UserType">
                             84                  <xs:restriction base="xs:string">
                             85                          <xs:minLength value="1"/>
                             86                          <xs:maxLength value="10"/>
                             87                  </xs:restriction>
                             88          </xs:simpleType>
                             89          <xs:simpleType name="RequestorType">
                             90                  <xs:restriction base="xs:string">
                             91                          <xs:minLength value="0"/>
                             92                          <xs:maxLength value="128"/>
                             93                  </xs:restriction>
                             94          </xs:simpleType>
                             95          <xs:simpleType name="CostCenterType">
                             96                  <xs:restriction base="xs:string">
                             97                          <xs:minLength value="1"/>
                             98                          <xs:maxLength value="4"/>
                             99                          <xs:enumeration value=""/>
                            100                          <xs:enumeration value="A0"/>
                            101                          <xs:enumeration value="A10"/>
                            102                          <xs:enumeration value="A20"/>
                            103                          <xs:enumeration value="A30"/>
                            104                          <xs:enumeration value="A40"/>
                            105                          <xs:enumeration value="A50"/>
                            106                          <xs:enumeration value="A60"/>
                            107                          <xs:enumeration value="A70"/>
                            108                          <xs:enumeration value="A80"/>
                            109                          <xs:enumeration value="A90"/>
                            110                          <xs:enumeration value="A100"/>
                            111                          <xs:enumeration value="A110"/>
                            112                  </xs:restriction>
                            113          </xs:simpleType>
                            114          <xs:simpleType name="PurchaseOrderNumberType">
                            115                  <xs:restriction base="xs:integer"/>
                            116          </xs:simpleType>
                            117          <xs:simpleType name="SpecialInstructionsType">
                            118                  <xs:restriction base="xs:string">
                            119                          <xs:minLength value="0"/>
                            120                          <xs:maxLength value="1000"/>
                            121                  </xs:restriction>
                            122          </xs:simpleType>
                            123          <xs:simpleType name="NameType">
                            124                  <xs:restriction base="xs:string">
                            125                          <xs:minLength value="1"/>
                            126                          <xs:maxLength value="20"/>
                            127                  </xs:restriction>
                            128          </xs:simpleType>
                            129          <xs:simpleType name="AddressType">
                            130                  <xs:restriction base="xs:string">
                            131                          <xs:minLength value="1"/>
                            132                          <xs:maxLength value="256"/>
                            133                  </xs:restriction>
                            134          </xs:simpleType>
                            135          <xs:simpleType name="TelephoneType">
                            136                  <xs:restriction base="xs:string">
                            137                          <xs:minLength value="1"/>
                            138                          <xs:maxLength value="24"/>
                            139                  </xs:restriction>
                            140          </xs:simpleType>
                            141          <xs:simpleType name="DateType">
                            142                  <xs:restriction base="xs:date"/>
                            143          </xs:simpleType>
                            144          <xs:simpleType name="CommentsType">
                            145                  <xs:restriction base="xs:string">
                            146                          <xs:minLength value="1"/>
                            147                          <xs:maxLength value="1000"/>
                            148                  </xs:restriction>
                            149          </xs:simpleType>
                            150          <xs:simpleType name="DescriptionType">
                            151                  <xs:restriction base="xs:string">
                            152                          <xs:minLength value="1"/>
                            153                          <xs:maxLength value="128"/>
                            154                  </xs:restriction>
                            155          </xs:simpleType>
                            156          <xs:simpleType name="UPCCodeType">
                            157                  <xs:restriction base="xs:string">
                            158                          <xs:minLength value="11"/>
                            159                          <xs:maxLength value="14"/>
                            160                          <xs:pattern value="\d{11}"/>
                            161                          <xs:pattern value="\d{12}"/>
                            162                          <xs:pattern value="\d{13}"/>
                            163                          <xs:pattern value="\d{14}"/>
                            164                  </xs:restriction>
                            165          </xs:simpleType>
                            166  </xs:schema>';
                            167    :INSTANCE :=
                            168  '<PurchaseOrder>
                            169     <Reference>ABULL-20100809203001136PDT</Reference>
                            170     <Actions>
                            171        <Action>
                            172           <User>ACABRIO</User>
                            173        </Action>
                            174     </Actions>
                            175     <Rejection/>
                            176     <Requestor>Alexis Bull</Requestor>
                            177     <User>ABULL</User>
                            178     <CostCenter>A50</CostCenter>
                            179     <ShippingInstructions>
                            180        <name>Alexis Bull</name>
                            181        <address>2011 Interiors Blvd,
                            182  South San Francisco,
                            183  California 99236
                            184  United States of America</address>
                            185        <telephone>950-720-3387</telephone>
                            186     </ShippingInstructions>
                            187     <SpecialInstructions>COD</SpecialInstructions>
                            188     <LineItems>
                            189        <LineItem ItemNumber="1" >
                            190           <Part Description="Scary Movie" UnitPrice="19.95">717951004857</Part>
                            191           <Quantity>5.0</Quantity>
                            192        </LineItem>
                            193        <LineItem ItemNumber="2" >
                            194           <Part Description="The Faculty" UnitPrice="19.95">717951002280</Part>
                            195           <Quantity>2.0</Quantity>
                            196        </LineItem>
                            197        <LineItem ItemNumber="3">
                            198           <Part Description="Phantom of the Paradise" UnitPrice="27.95">24543023777</Part>
                            199           <Quantity>3.0</Quantity>
                            200        </LineItem>
                            201     </LineItems>
                            202  </PurchaseOrder>';
                            203  end;
                            204  /
                            
                            PL/SQL procedure successfully completed.
                            
                            Elapsed: 00:00:00.00
                            SQL> declare
                              2    V_XML_SCHEMA xmlType := XMLType(:XMLSCHEMA1);end;
                              3  /
                            SP2-0552: Bind variable "XMLSCHEMA1" not declared.
                            Elapsed: 00:00:00.00
                            SQL> --
                            SQL> declare
                              2    V_XMLSCHEMA XMLTYPE := XMLTYPE(:XMLSCHEMA);
                              3  begin
                              4    DBMS_XMLSCHEMA.registerSchema(
                              5      schemaURL => 'http://localhost:80/home/SCOTT/poSource/xsd/purchaseOrder.xsd',
                              6      schemaDoc => V_XMLSCHEMA,
                              7      local     => TRUE,
                              8      genTypes  => TRUE,
                              9      genTables => FALSE
                             10    );
                             11  end;
                             12  /
                            
                            PL/SQL procedure successfully completed.
                            
                            Elapsed: 00:00:00.35
                            SQL> create table PURCHASEORDER
                              2         of XMLTYPE
                              3         XMLSCHEMA "http://localhost:80/home/SCOTT/poSource/xsd/purchaseOrder.xsd" ELEMENT "PurchaseOrder"
                              4  /
                            
                            Table created.
                            
                            Elapsed: 00:00:00.10
                            SQL> call DBMS_XMLSTORAGE_MANAGE.renameCollectionTable (USER,'PURCHASEORDER',NULL,'/PurchaseOrder/LineItems/LineItem','LINEITEM_TABLE',NULL)
                              2  /
                            
                            Call completed.
                            
                            Elapsed: 00:00:00.87
                            SQL> call DBMS_XMLSTORAGE_MANAGE.renameCollectionTable (USER,'PURCHASEORDER',NULL,'/PurchaseOrder/Actions/Action','ACTION_TABLE',NULL)
                              2  /
                            
                            Call completed.
                            
                            Elapsed: 00:00:00.09
                            SQL> desc PURCHASEORDER
                             Name                                      Null?    Type
                             ----------------------------------------- -------- ----------------------------
                            TABLE of SYS.XMLTYPE(XMLSchema "http://localhost:80/home/SCOTT/poSource/xsd/purchaseOrder.xsd" Element "PurchaseOrder") STORAGE Object-relational TYPE "Purchase
                            OrderType667_T"
                            
                            SQL> --
                            SQL> insert into PURCHASEORDER values (XMLTYPE(:INSTANCE))
                              2  /
                            
                            1 row created.
                            
                            Elapsed: 00:00:00.44
                            SQL> commit
                              2  /
                            
                            Commit complete.
                            
                            Elapsed: 00:00:00.00
                            SQL> create or replace type ACTION_T as object (
                              2    USER_NAME                                          VARCHAR2(10 CHAR),
                              3    ACTION_DATE                                        DATE
                              4  )
                              5  /
                            
                            Type created.
                            
                            Elapsed: 00:00:00.02
                            SQL> show errors
                            No errors.
                            SQL> /
                            
                            Type created.
                            
                            Elapsed: 00:00:00.01
                            SQL> create or replace type ACTION_V as VARRAY(32767) of ACTION_T
                              2  /
                            
                            Type created.
                            
                            Elapsed: 00:00:00.01
                            SQL> show errors
                            No errors.
                            SQL> --
                            SQL> create or replace type ACTIONS_T as object (
                              2    ACTION  ACTION_V
                              3  )
                              4  /
                            
                            Type created.
                            
                            Elapsed: 00:00:00.01
                            SQL> show errors
                            No errors.
                            SQL> --
                            SQL> create or replace type REJECTION_T as object (
                              2    USER_NAME                                          VARCHAR2(10 CHAR),
                              3    REJECTION_DATE                                     DATE,
                              4    COMMENTS                                           VARCHAR2(1000 CHAR)
                              5  )
                              6  /
                            
                            Type created.
                            
                            Elapsed: 00:00:00.01
                            SQL> show errors
                            No errors.
                            SQL> --
                            SQL> create or replace type SHIPPING_INSTRUCTIONS_T as object (
                              2    NAME                                               VARCHAR2(20 CHAR),
                              3    ADDRESS                                            VARCHAR2(256 CHAR),
                              4    TELEPHONE                                          VARCHAR2(24 CHAR)
                              5  )
                              6  /
                            
                            Type created.
                            
                            Elapsed: 00:00:00.01
                            SQL> show errors
                            No errors.
                            SQL> --
                            SQL> create or replace type PART_T as object (
                              2   PART_TEXT                                          VARCHAR2(14 CHAR),
                              3   DESCRIPTION                                        VARCHAR2(128 CHAR),
                              4   UNITPRICE                                          NUMBER(14,2)
                              5  )
                              6  /
                            
                            Type created.
                            
                            Elapsed: 00:00:00.01
                            SQL> show errors
                            No errors.
                            SQL> /
                            
                            Type created.
                            
                            Elapsed: 00:00:00.00
                            SQL> create or replace type LINEITEM_T as object (
                              2    ITEMNUMBER                                         NUMBER(38),
                              3    PART                                               PART_T,
                              4    QUANTITY                                           NUMBER(12,4)
                              5  )
                              6  /
                            
                            Type created.
                            
                            Elapsed: 00:00:00.01
                            SQL> show errors
                            No errors.
                            SQL> /
                            
                            Type created.
                            
                            Elapsed: 00:00:00.00
                            SQL> create or replace type LINEITEM_V as VARRAY(32767) of LINEITEM_T
                              2  /
                            
                            Type created.
                            
                            Elapsed: 00:00:00.01
                            SQL> show errors
                            No errors.
                            SQL> --
                            SQL> create or replace type LINEITEMS_T as object (
                              2    LINEITEM  LINEITEM_V
                              3  )
                              4  /
                            
                            Type created.
                            
                            Elapsed: 00:00:00.01
                            SQL> show errors
                            No errors.
                            SQL> --
                            SQL> create or replace type PURCHASEORDER_T as object (
                              2   DATECREATED                                        TIMESTAMP(6),
                              3   REFERENCE                                          VARCHAR2(30 CHAR),
                              4   ACTIONS                                            ACTIONS_T,
                              5   REJECTION                                          REJECTION_T,
                              6   REQUESTOR                                          VARCHAR2(128 CHAR),
                              7   USER_NAME                                          VARCHAR2(10 CHAR),
                              8   COSTCENTER                                         VARCHAR2(4 CHAR),
                              9   SHIPPINGINSTRUCTIONS                               SHIPPING_INSTRUCTIONS_T,
                             10   SPECIALINSTRUCTIONS                                VARCHAR2(1000 CHAR),
                             11   LINEITEMS                                          LINEITEMS_T
                             12  )
                             13  /
                            
                            Type created.
                            
                            Elapsed: 00:00:00.12
                            SQL> show errors
                            No errors.
                            SQL> --
                            SQL> select PURCHASEORDER_T (
                              2           DATECREATED,
                              3                                   REFERENCE,
                              4                                   ACTIONS_T(
                              5                                     CAST(
                              6               MULTISET(
                              7                 SELECT ACTION_T(
                              8                          USER_NAME,
                              9                          ACTION_DATE
                             10                        )
                             11                   FROM XMLTABLE(
                             12                         '/Actions/Action'
                             13                          passing ACTIONS
                             14                          columns
                             15                            USER_NAME        VARCHAR2(10 CHAR) path 'User',
                             16                            ACTION_DATE      DATE              path 'Date'
                             17                        )
                             18               ) AS ACTION_V
                             19             )
                             20           ),
                             21           (
                             22                             select REJECTION_T (
                             23                      USER_NAME,
                             24                      REJECTION_DATE,
                             25                      COMMENTS
                             26                    )
                             27               from XMLTABLE(
                             28                      '/Rejection'
                             29                      passing REJECTION
                             30                      columns
                             31                        USER_NAME         VARCHAR2(10 CHAR)     path 'User',
                             32                        REJECTION_DATE    DATE                  path 'Date',
                             33                        COMMENTS          VARCHAR2(1000 CHAR)   path 'Comments'
                             34                    )
                             35           ),
                             36                                   REQUESTOR,
                             37                                   USER_NAME,
                             38           COSTCENTER,
                             39                           (
                             40                             select SHIPPING_INSTRUCTIONS_T (
                             41                      USER_NAME,
                             42                      ADDRESS,
                             43                      TELEPHONE
                             44                    )
                             45               from XMLTABLE(
                             46                      '/ShippingInstructions'
                             47                      passing SHIPPING_INSTRUCTIONS
                             48                      columns
                             49                        USER_NAME       VARCHAR2(20 CHAR)    path 'name',
                             50                        ADDRESS         VARCHAR2(256 CHAR)   path 'address',
                             51                        TELEPHONE       VARCHAR2(24 CHAR)    path 'telephone'
                             52                    )
                             53           ),
                             54           SPECIAL_INSTRUCTIONS,
                             55                                   LINEITEMS_T(
                             56                                     CAST(
                             57               MULTISET(
                             58                 SELECT LINEITEM_T (
                             59                          ITEMNUMBER,
                             60                          (
                             61                            select PART_T(
                             62                                     PART_TEXT,
                             63                                     DESCRIPTION,
                             64                                     UNITPRICE
                             65                                   )
                             66                              from XMLTABLE(
                             67                                     '/Part'
                             68                                     passing PART
                             69                                     columns
                             70                                       PART_TEXT          VARCHAR2(14 CHAR)  path 'text()',
                             71                                       DESCRIPTION        VARCHAR2(128 CHAR) path '@Description',
                             72                                       UNITPRICE          NUMBER(14,2)       path '@UnitPrice'
                             73                                   )
                             74                          ),
                             75                          QUANTITY
                             76                        )
                             77                   FROM XMLTABLE(
                             78                         '/LineItems/LineItem'
                             79                          passing LINEITEMS
                             80                          columns
                             81                            ITEMNUMBER   NUMBER(38)   path '@ItemNumber',
                             82                            PART         XMLTYPE      path 'Part',
                             83                            QUANTITY     NUMBER(12,4) path 'Quantity'
                             84                        )
                             85               ) AS LINEITEM_V
                             86             )
                             87           )
                             88         )
                             89    from PURCHASEORDER,
                             90         XMLTABLE(
                             91           '/PurchaseOrder'
                             92           passing OBJECT_VALUE
                             93           columns
                             94              DATECREATED               TIMESTAMP(6)        path '@DateCreated',
                             95                                                  REFERENCE                 VARCHAR2(30 CHAR)   path 'Reference',
                             96                                                  ACTIONS                   XMLTYPE             path 'Actions',
                             97                                                  REJECTION                 XMLTYPE             path 'Rejection',
                             98                                                  REQUESTOR                 VARCHAR2(128 CHAR)  path 'Requestor',
                             99                                                  USER_NAME                 VARCHAR2(10 CHAR)   path 'User',
                            100                                                  COSTCENTER                VARCHAR2(4 CHAR)    path 'CostCenter',
                            101                                                  SHIPPING_INSTRUCTIONS     XMLTYPE             path 'ShippingInstructions',
                            102                                                  SPECIAL_INSTRUCTIONS      VARCHAR2(1000 CHAR) path 'SpecialInstructions',
                            103                                                  LINEITEMS                 XMLType             path 'LineItems'
                            104                     )
                            105  /
                            
                            PURCHASEORDER_T(DATECREATED,REFERENCE,ACTIONS_T(CAST(MULTISET(SELECTACTION_T(USE
                            --------------------------------------------------------------------------------
                            PURCHASEORDER_T(NULL, 'ABULL-20100809203001136PDT', ACTIONS_T(ACTION_V(ACTION_T(
                            'ACABRIO', NULL))), REJECTION_T(NULL, NULL, NULL), 'Alexis Bull', 'ABULL', 'A50'
                            , SHIPPING_INSTRUCTIONS_T('Alexis Bull', '2011 Interiors Blvd,
                            South San Francisco,
                            California 99236
                            United States of America', '950-720-3387'), 'COD', LINEITEMS_T(LINEITEM_V(LINEIT
                            EM_T(1, PART_T('717951004857', 'Scary Movie', 19.95), 5), LINEITEM_T(2, PART_T('
                            717951002280', 'The Faculty', 19.95), 2), LINEITEM_T(3, PART_T('24543023777', 'P
                            hantom of the Paradise', 27.95), 3))))
                            
                            
                            Elapsed: 00:00:00.11
                            SQL> quit
                            Edited by: mdrake on May 13, 2013 11:33 PM
                            • 11. Re: Partial XMLType toObject
                              odie_63
                              mdrake wrote:
                              If you have access to metalink see Doc ID 1504299.1
                              Mark,

                              Thanks for sharing that. I always thought it was supported.
                              I guess the few examples I already gave using that technique are to be scrapped then? :)

                              e.g. {thread:id=2468151}

                              I fully agree with the OP though, how come this is not clearly documented as a deprecated feature?
                              • 12. Re: Partial XMLType toObject
                                1008638
                                Perfect, thank you.
                                • 13. Re: Partial XMLType toObject
                                  mdrake-Oracle
                                  We have always stated that you should not write code that relies upon the name or shape of the SQL Objects generated by Schema Registration, which implies that any API that uses them should be not be used. In reality, in the early days the API was there as a back-door in case of issues with using the XML abstraction. It was meant to be used as stop-gap measure, under the direction of support or development, to allow someone a temporary workaround while we fixed issues with the operators like extract or extractValue.
                                  • 14. Re: Partial XMLType toObject
                                    odie_63
                                    mdrake wrote:
                                    We have always stated that you should not write code that relies upon the name or shape of the SQL Objects generated by Schema Registration.
                                    I know, and I learnt that from the forum (from you or Marco I don't remember).

                                    However, in normal circumstances, one should probably learn it from the official XML DB documentation, but the only reference I know of is :
                                    http://docs.oracle.com/cd/E11882_01/appdev.112/e23094/xdb04cre.htm#CHDICCHC
                                    More generally, in your code, do not rely on the current mapping between the XML Schema object model and the SQL object model. This Oracle XML DB implementation mapping might change in the future.
                                    which is very clear, but also very easily missed.

                                    In reality, in the early days the API was there as a back-door in case of issues with using the XML abstraction. It was meant to be used as stop-gap measure, under the direction of support or development, to allow someone a temporary workaround while we fixed issues with the operators like extract or extractValue.
                                    That I understand but now, IMO, the documentation really needs a fix, not just a MOS note that most people won't be aware of.
                                    1 2 Previous Next