This discussion is archived
9 Replies Latest reply: Mar 6, 2013 1:37 PM by 994903 RSS

Populate collection type from XMLType

994903 Newbie
Currently Being Moderated
Oracle Database 11g Enterprise Edition Release 11.1.0.7.0 - 64bit Production
PL/SQL Release 11.1.0.7.0 - Production
CORE     11.1.0.7.0     Production
TNS for Linux: Version 11.1.0.7.0 - Production
NLSRTL Version 11.1.0.7.0 - Production

Please bear with me as I'm new to this XML DB thing and also this is my first post.
I'm trying to populate collection type from XMLType. I was able to populate a table from XMLType but
couldn't figure out a way to populate the collection type. Here is the description of my problem:

Object Type:
CREATE OR REPLACE TYPE DOC_ROWTYPE AS OBJECT
(
     REFERENCENUMBER VARCHAR2(255),
     REQID NUMBER(12),
     REQDETID NUMBER(12),
     FROMAMOUNT VARCHAR2(31),
     TOAMOUNT VARCHAR2(31),
     TOACCOUNTID NUMBER(12),
     TOACCOUNTNUMBER VARCHAR2(35),
     FROMACCOUNTID NUMBER(12),
     FROMACCOUNTNUMBER VARCHAR2(35),
);

Collection Type:
CREATE OR REPLACE TYPE DOC_TABLETYPE IS TABLE OF DOC_ROWTYPE;

I have a physical table which is created when I registered a schema.
A table (Temp_Result) got created with column SYS_NC_ROWINFO$ which is of XMLType.
As you can see this is only a temporary table which will store the response XML which I want to finally get it to collection type.

XML to parse:
<code>
<TFSResponse>
<TFS>
<refNumber>12345</refNumber>
<reqId>123</reqId>
<reqDetId>111</reqDetId>
<fromAmount>20</fromAmount>
<toAmount>20</toAmount>
<fromAccount>
<accountId>22222</id>
<accountNumber>12345678</number>
</fromAccount>
<toAccount>
<accountId>33333</id>
<accountNumber>123456789</number>
</toAccount>
</TFS>
.... many TFS Tags
</TFSResponse>
</code>

So each object in the collection is one TFS tag.
Any advice on how to implement this?
  • 1. Re: Populate collection type from XMLType
    odie_63 Guru
    Currently Being Moderated
    Hi, Welcome to the forum!

    First question : did you create the object and collection types manually or were they created by the registration process? Could you post the schema?

    Second question : what do you intend to do after you get the data in the collection? Item-by-item processing? Send the collection to another process?

    Edited by: odie_63 on 5 mars 2013 17:06
  • 2. Re: Populate collection type from XMLType
    994903 Newbie
    Currently Being Moderated
    Thanks for your quick reply.
    Yes the object and collection type were created manually. Here is the PL/SQL block I used to register the schema:
    DECLARE
    l_schema CLOB;
    BEGIN
    l_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">

    <xs:element name="TFSResponse" type="TFSResponseWSResponseType" xdb:defaultTable="TEMP_RESULT" />
    <xs:complexType name="TFSResponseWSResponseType">
    <xs:sequence>
    <xs:element maxOccurs="unbounded" name="TFS" type="TFSSummaryWSTO"/>
    </xs:sequence>
    </xs:complexType>

    <xs:complexType name="TFSSummaryWSTO">
    <xs:sequence>
    <xs:element name="referenceNumber" type="xs:string"/>
    <xs:element name="reqId" type="xs:long"/>
    <xs:element name="reqDetId" type="xs:long"/>
    <xs:element name="fromAccount" type="AccountSummaryWSTO" />
    <xs:element name="toAccount" type="AccountSummaryWSTO"/>
    <xs:element name="fromAmount" type="xs:decimal"/>
    <xs:element name="toAmount" type="xs:decimal"/>
    </xs:sequence>
    </xs:complexType>

    <xs:complexType name="AccountSummaryWSTO">
    <xs:sequence>
    <xs:element minOccurs="1" name="accountId" type="xs:long" />
    <xs:element minOccurs="1" name="accountNumber" type="xs:string" />
    </xs:sequence>
    </xs:complexType>
    </xs:schema>';

    dbms_xmlSchema.registerSchema(schemaurl => 'TFSWebService_schema.xsd',
    schemadoc => l_schema,
    enableHierarchy => dbms_xmlschema.ENABLE_HIERARCHY_NONE);
    END;/

    To answer your second question, I have a bigger stored procedure of which this XML parsing is only a small part. The output of this stored proc is a cursor which points to this collection type. Further this collection has already some data populated into it by the stored proc. So I have to add on the data from this XML. This stored proc is invoked by a reporting engine.
  • 3. Re: Populate collection type from XMLType
    odie_63 Guru
    Currently Being Moderated
    Yes the object and collection type were created manually.
    Maybe you already know that but the schema registration also create object types and collections to map the XML data model to the SQL data model.
    In your case, the objects are very similar to your existing ones.
    For example, on my test db :
    SQL> desc "TFSSummaryWSTO922_T"
     "TFSSummaryWSTO922_T" is NOT FINAL
     Name                                      Null?    Type
     ----------------------------------------- -------- ----------------------------
     SYS_XDBPD$                                         XDB.XDB$RAW_LIST_T
     referenceNumber                                    VARCHAR2(4000 CHAR)
     reqId                                              NUMBER(20)
     reqDetId                                           NUMBER(20)
     fromAccount                                        AccountSummaryWSTO921_T
     toAccount                                          AccountSummaryWSTO921_T
     fromAmount                                         NUMBER
     toAmount                                           NUMBER
    The names may differ on your instance since they're system-generated but we can control them via a set of xdb annotations just like you did with xdb:defaultTable.

    The purpose of my first question was to determine if you can use those generated types instead of the existing ones, and directly parse the XML into the collection (thus bypassing the INSERT step).
    Now that I see the schema and your explanation about the procedure, the answer is clearly no because the collections don't share the same structure.

    Further this collection has already some data populated into it by the stored proc. So I have to add on the data from this XML.
    OK, so here's a possible solution :
    select DOC_ROWTYPE(
             x.REFERENCENUMBER
           , x.REQID
           , x.REQDETID
           , x.FROMAMOUNT
           , x.TOAMOUNT
           , x.TOACCOUNTID
           , x.TOACCOUNTNUMBER
           , x.FROMACCOUNTID
           , x.FROMACCOUNTNUMBER
           )
    bulk collect into v_new_xml_collection
    from temp_result t
       , xmltable(
           '/TFSResponse/TFS'
           passing t.object_value
           columns REFERENCENUMBER   VARCHAR2(255)  path 'referenceNumber'
                 , REQID             NUMBER(12)     path 'reqId'
                 , REQDETID          NUMBER(12)     path 'reqDetId'
                 , FROMAMOUNT        VARCHAR2(31)   path 'fromAmount'
                 , TOAMOUNT          VARCHAR2(31)   path 'toAmount'
                 , TOACCOUNTID       NUMBER(12)     path 'toAccount/accountId'
                 , TOACCOUNTNUMBER   VARCHAR2(35)   path 'toAccount/accountNumber'
                 , FROMACCOUNTID     NUMBER(12)     path 'fromAccount/accountId'
                 , FROMACCOUNTNUMBER VARCHAR2(35)   path 'fromAccount/accountNumber'
         ) x ;
    
    
      v_existing_collection := v_existing_collection MULTISET UNION ALL v_new_xml_collection ;
    The output of this stored proc is a cursor which points to this collection type.
    So I guess you're using the TABLE() operator?
    Unless you're doing additional processing on the data, you can also keep the data in the TEMP_RESULT table and perform a UNION ALL in SQL, something like this :
      OPEN my_refcursor FOR
      SELECT ...
      FROM TABLE(v_existing_collection)
     UNION ALL
     SELECT ...
     FROM temp_result t
         , XMLTable ( ... )
    Just a suggestion off the top of my head, of course I have no idea about the whole design of the procedure :)

    As a general rule, I would just avoid moving data back and forth unnecessarily from SQL to the PL/SQL engine :
    XMLType (PL/SQL) --> TEMP_RESULT TABLE (SQL) --> collection (PL) --> cursor (SQL)

    That's a waste of resources.

    Edited by: odie_63 on 5 mars 2013 18:25
  • 4. Re: Populate collection type from XMLType
    994903 Newbie
    Currently Being Moderated
    Hi Odie,

    I tried as you said. My XML is much larger than what I posted. I kept it short for brevity.
    So I had to do all the manual drudge work of adding the xpaths and columns. I got the below compilation errors when I run the stored proc.

    [Error] PLS-00316: PLS-00316: PL/SQL TABLEs must use a single index (I'm getting this error irrespective of using 'x')
    [Error] ORA-00904: PL/SQL: ORA-00904: : invalid identifier (This might be because of some column name mismatch but I couldn't find it)
    [Error] PLS-00306: PLS-00306: wrong number or types of arguments in call to 'MULTISET_UNION_ALL'

    First two errors are this line
    select DOC_ROWTYPE(...) bulk collect into v_new_xml_collection from temp_result t , xmltable(....) x;

    Second error as is abvious is from this line:
    v_existing_collection := v_existing_collection MULTISET UNION ALL v_new_xml_collection ;

    I'm getting the same errors when I did using the UNION ALL approach. Any pointers?
  • 5. Re: Populate collection type from XMLType
    odie_63 Guru
    Currently Being Moderated
    991900 wrote:
    I'm getting the same errors when I did using the UNION ALL approach. Any pointers?
    Sorry, I'm afraid I can't say much by just looking at some pseudo code.
    Unless you can post a relevant part of the procedure, you're on your own.

    Try resolving the ORA-00904 first, it might solve the rest.
  • 6. Re: Populate collection type from XMLType
    mdrake Expert
    Currently Being Moderated
    Does this help
    SQL> CREATE OR REPLACE TYPE ACCOUNT_T as OBJECT (
      2    "accountId"       NUMBER(12),
      3    "accountNumber"   VARCHAR2(35)
      4  )
      5  /
    
    Type created.
    
    SQL> show errors
    No errors.
    SQL> --
    SQL> CREATE OR REPLACE TYPE TFS AS OBJECT(
      2     "refNumber"  VARCHAR2(255),
      3     "reqId"      NUMBER(12),
      4     "reqDetId"   NUMBER(12),
      5     "fromAmount" VARCHAR2(31),
      6     "toAmount"   VARCHAR2(31),
      7     "fromAccount" ACCOUNT_T,
      8     "toAccount"   ACCOUNT_T
      9  );
     10  /
    
    Type created.
    
    SQL> show errors
    No errors.
    SQL> --
    SQL> CREATE OR REPLACE TYPE TFS_C
      2      as TABLE of TFS
      3  /
    
    Type created.
    
    SQL> show errors
    No errors.
    SQL> --
    SQL> CREATE OR REPLACE Type TFS_RESPONSE_T as OBJECT(
      2    "TFSResponse" TFS_C
      3  );
      4  /
    
    Type created.
    
    SQL> show errors
    No errors.
    SQL> /
    
    Type created.
    
    SQL> CREATE OR REPLACE type CODE_T as OBJECT(
      2    "code" TFS_RESPONSE_T
      3  );
      4  /
    
    Type created.
    
    SQL> show errors
    No errors.
    SQL> --
    SQL>
    SQL> with "MY_XML" as
      2  (
      3    select XMLTYPE(
      4  '<code>
      5     <TFSResponse>
      6             <TFS>
      7                     <refNumber>12345</refNumber>
      8                     <reqId>123</reqId>
      9                     <reqDetId>111</reqDetId>
     10                     <fromAmount>20</fromAmount>
     11                     <toAmount>20</toAmount>
     12                     <fromAccount>
     13                             <accountId>22222</accountId>
     14                             <accountNumber>12345678</accountNumber>
     15                     </fromAccount>
     16                     <toAccount>
     17                             <accountId>33333</accountId>
     18                             <accountNumber>123456789</accountNumber>
     19                     </toAccount>
     20             </TFS>
     21             <TFS>
     22                     <refNumber>12346</refNumber>
     23                     <reqId>123</reqId>
     24                     <reqDetId>111</reqDetId>
     25                     <fromAmount>20</fromAmount>
     26                     <toAmount>20</toAmount>
     27                     <fromAccount>
     28                             <accountId>22222</accountId>
     29                             <accountNumber>12345678</accountNumber>
     30                     </fromAccount>
     31                     <toAccount>
     32                             <accountId>33333</accountId>
     33                             <accountNumber>123456789</accountNumber>
     34                     </toAccount>
     35             </TFS>
     36             <TFS>
     37                     <refNumber>12347</refNumber>
     38                     <reqId>123</reqId>
     39                     <reqDetId>111</reqDetId>
     40                     <fromAmount>20</fromAmount>
     41                     <toAmount>20</toAmount>
     42                     <fromAccount>
     43                             <accountId>22222</accountId>
     44                             <accountNumber>12345678</accountNumber>
     45                     </fromAccount>
     46                     <toAccount>
     47                             <accountId>33333</accountId>
     48                             <accountNumber>123456789</accountNumber>
     49                     </toAccount>
     50             </TFS>
     51     </TFSResponse>
     52  </code>') as "XML"
     53    from DUAL
     54  )
     55  select
     56    "TMOBILE"."CODE_T"(
     57      "TMOBILE"."TFS_RESPONSE_T"(
     58        CAST(
     59          MULTISET(
     60            select
     61              "TMOBILE"."TFS"(
     62                "refNumber_000002",
     63                "reqId_000003",
     64                 "reqDetId_000004",
     65                "fromAmount_000005",
     66                "toAmount_000006",
     67                "TMOBILE"."ACCOUNT_T"(
     68                  "accountId_000007",
     69                  "accountNumber_000008"
     70                ),
     71                 "TMOBILE"."ACCOUNT_T"(
     72                  "accountId_000009",
     73                  "accountNumber_000010"
     74                )
     75              )
     76              FROM
     77                XMLTABLE(
     78                  '/TFS'
     79                  passing "TFSResponse_000001"
     80                   COLUMNS
     81                   "refNumber_000002"                  VARCHAR2(255)                       PATH 'refNumber',
     82                   "reqId_000003"                      NUMBER                              PATH 'reqId',
     83                    "reqDetId_000004"                   NUMBER                              PATH 'reqDetId',
     84                   "fromAmount_000005"                 VARCHAR2(31)                        PATH 'fromAmount',
     85                    "toAmount_000006"                   VARCHAR2(31)                        PATH 'toAmount',
     86                     "accountId_000007"                  NUMBER                              PATH 'fromAccount/accountId',
     87                      "accountNumber_000008"              VARCHAR2(35)                        PATH 'fromAccount/accountNumber',
     88                     "accountId_000009"                  NUMBER                              PATH 'toAccount/accountId',
     89                      "accountNumber_000010"              VARCHAR2(35)                        PATH 'toAccount/accountNumber'
     90                )
     91          ) as "TMOBILE"."TFS_C"
     92        )
     93      )
     94    )
     95    FROM MY_XML,
     96      XMLTABLE(
     97        '/'
     98        passing "XML"
     99        COLUMNS
    100              "TFSResponse_000001"                XMLTYPE                             PATH 'code/TFSResponse/TFS'
    101       )
    102
    SQL> /
    CODE_T(TFS_RESPONSE_T(TFS_C(TFS('12345', 123, 111, '20', '20', ACCOUNT_T(22222, '12345678'), ACCOUNT_T(33333, '123456789')), TFS('12346', 123, 111, '20', '20',
    ACCOUNT_T(22222, '12345678'), ACCOUNT_T(33333, '123456789')), TFS('12347', 123, 111, '20', '20',
     ACCOUNT_T(22222, '12345678'), ACCOUNT_T(33333, '123456789')))))
    
    
    SQL>
  • 7. Re: Populate collection type from XMLType
    994903 Newbie
    Currently Being Moderated
    Thanks mdrake for your solution but I didn't tried this yet. Since the solution given by odie seems to be working with only one problem.
    I was able to get rid of the first two errors. I'm having problem joining the two collections.

    Existing Object Type:
    CREATE OR REPLACE TYPE DOC_ROWTYPE AS OBJECT
    (
    REFERENCENUMBER VARCHAR2(255),
    REQID NUMBER(12),
    REQDETID NUMBER(12),
    FROMAMOUNT VARCHAR2(31),
    TOAMOUNT VARCHAR2(31),
    TOACCOUNTID NUMBER(12),
    TOACCOUNTNUMBER VARCHAR2(35),
    FROMACCOUNTID NUMBER(12),
    FROMACCOUNTNUMBER VARCHAR2(35),
    );

    Existing Collection Type:
    CREATE OR REPLACE TYPE DOC_TABLETYPE IS TABLE OF DOC_ROWTYPE;

    New Object type to hold XML data:
    CREATE OR REPLACE TYPE DOC_ROWTYPE_XML AS OBJECT
    (
    REFERENCENUMBER VARCHAR2(255),
    REQID NUMBER(12),
    REQDETID NUMBER(12),
    FROMAMOUNT VARCHAR2(31),
    TOAMOUNT VARCHAR2(31),
    TOACCOUNTID NUMBER(12),
    TOACCOUNTNUMBER VARCHAR2(35),
    FROMACCOUNTID NUMBER(12),
    FROMACCOUNTNUMBER VARCHAR2(35),
    );

    New Collection Type to hold XML data:
    CREATE OR REPLACE TYPE DOC_TABLETYPE_XML IS TABLE OF DOC_ROWTYPE_XML;

    Parse the XML response. This is also working fine.
    select DOC_ROWTYPE_XML(
    x.REFERENCENUMBER
    , x.REQID
    , x.REQDETID
    , x.FROMAMOUNT
    , x.TOAMOUNT
    , x.TOACCOUNTID
    , x.TOACCOUNTNUMBER
    , x.FROMACCOUNTID
    , x.FROMACCOUNTNUMBER
    )
    bulk collect into DOC_TABLETYPE_XML
    from temp_result t
    , xmltable(
    '/TFSResponse/TFS'
    passing t.object_value
    columns REFERENCENUMBER VARCHAR2(255) path 'referenceNumber'
    , REQID NUMBER(12) path 'reqId'
    , REQDETID NUMBER(12) path 'reqDetId'
    , FROMAMOUNT VARCHAR2(31) path 'fromAmount'
    , TOAMOUNT VARCHAR2(31) path 'toAmount'
    , TOACCOUNTID NUMBER(12) path 'toAccount/accountId'
    , TOACCOUNTNUMBER VARCHAR2(35) path 'toAccount/accountNumber'
    , FROMACCOUNTID NUMBER(12) path 'fromAccount/accountId'
    , FROMACCOUNTNUMBER VARCHAR2(35) path 'fromAccount/accountNumber'
    ) x ;


    DOC_TABLETYPE := DOC_TABLETYPE MULTISET UNION ALL DOC_TABLETYPE_XML;

    This one is giving the below compilation error:
    *[Error] PLS-00306: wrong number or types of arguments in call to 'MULTISET_UNION_ALL'*

    The output of my stored procedure as I said is a cursor. If I pass either of the collection to the cursor it works. But I need to pass the join of the collections.
    OPEN result_cursor FOR SELECT * FROM TABLE (DOC_TABLETYPE_XML );
  • 8. Re: Populate collection type from XMLType
    odie_63 Guru
    Currently Being Moderated
    I'm not sure if you're still posting pseudo-code here, but if not you're missing some basic PL/SQL concepts.
    DOC_TABLETYPE := DOC_TABLETYPE MULTISET UNION ALL DOC_TABLETYPE_XML;
    That doesn't work for two reasons :

    1- DOC_TABLETYPE and DOC_TABLETYPE_XML are type names, not instances. They do not hold any data. (again if that's just pseudocode, it's very confusing)
    2- the two collections must be of the same datatype

    So, in short, you don't have to create another specific SQL object (and collection). Just use the existing one all along.
      /* somewhere in the declare section */
      
      -- existing instance of your collection
      v_existing_coll  DOC_TABLETYPE;
      
      -- instance of DOC_TABLETYPE to hold data from XML
      v_xml_coll   DOC_TABLETYPE;
    
    BEGIN
    
      select DOC_ROWTYPE(
               x.REFERENCENUMBER
             , x.REQID
             , x.REQDETID
             , x.FROMAMOUNT
             , x.TOAMOUNT
             , x.TOACCOUNTID
             , x.TOACCOUNTNUMBER
             , x.FROMACCOUNTID
             , x.FROMACCOUNTNUMBER
             )
      bulk collect into V_XML_COLL
      from temp_result t
         , xmltable(
             '/TFSResponse/TFS'
             passing t.object_value
             columns REFERENCENUMBER   VARCHAR2(255)  path 'referenceNumber'
                   , REQID             NUMBER(12)     path 'reqId'
                   , REQDETID          NUMBER(12)     path 'reqDetId'
                   , FROMAMOUNT        VARCHAR2(31)   path 'fromAmount'
                   , TOAMOUNT          VARCHAR2(31)   path 'toAmount'
                   , TOACCOUNTID       NUMBER(12)     path 'toAccount/accountId'
                   , TOACCOUNTNUMBER   VARCHAR2(35)   path 'toAccount/accountNumber'
                   , FROMACCOUNTID     NUMBER(12)     path 'fromAccount/accountId'
                   , FROMACCOUNTNUMBER VARCHAR2(35)   path 'fromAccount/accountNumber'
           ) x ;
     
     
      v_existing_coll := v_existing_coll MULTISET UNION ALL v_xml_coll;
  • 9. Re: Populate collection type from XMLType
    994903 Newbie
    Currently Being Moderated
    Thanks odie, it worked. The reason being I was using different data types for both collections.

Legend

  • Correct Answers - 10 points
  • Helpful Answers - 5 points