Forum Stats

  • 3,826,273 Users
  • 2,260,617 Discussions
  • 7,896,860 Comments

Discussions

Insert XML file into Relational database model - no XMLTYPE!

679560
679560 Member Posts: 4
edited Jan 13, 2009 6:37PM in XML DB
Dear all,
How can I store a known complex XML file into an existing relational database WITHOUT using xmltypes in the database ?

I read the article on DBMS_XMLSTORE. DBMS_XMLSTORE indeed partially bridges the gap between XML and RDBMS to a certain extent, namely for simply structured XML (canonical structure) and simple tables.
However, when the XML structure will become arbitrary and rapidly evolving, surely there must be a way to map XML to a relational model more flexibly.

We work in a java/Oracle10 environment that receives very large XML documents from an independent data management source. These files comply with an XML schema. That is all we know. Still, all these data must be inserted/updated daily in an existing relational model. Quite an assignment isn't it ?
The database does and will not contain XMLTYPES, only plain RDBMS tables.

Are you aware of a framework/product or tool to do what DBMS_XMLSTORE does but with any format of XML file ? If not, I am doomed.



Cheers.
Luc.

Edited by: user6693852 on Jan 13, 2009 7:02 AM
Tagged:

Answers

  • 407338
    407338 Member Posts: 852
    If you don't want to store it using XMLTYPE, you may store it as a CLOB in the table. I think you will receive the xml as a string, so you just store it as it is in the CLOB. Or you can create more tables and shred the xml and insert them into different tables or update the tables depending on your data. Is that what you are asking about?

    Ben
  • mdrake-Oracle
    mdrake-Oracle Member Posts: 5,904
    Let me rephrase your question. How can I store a Date in the database without using a Date type. I can store it as a string. Of course by doing so I can store dates that are in Month 13 and Feburary can have 31 days, but I can write code that will stop that happening - Sounds pretty stupid doesn't it..... :).

    Well the same applies to XML, why would you store it as CLOB or VARCHAR when it's not a string of text, it's a textual representation of an XML Object.

    And while I ranting, , this forum is about using XML DB and XMLType, not ways of avoiding using XML DB and XMLType.

    Now back to normal service. If what you are trying to do is popuiate relational data from the content of an XML document then the correct way to do this is..

    1. Register the XML Schema and allow it to create an XMLType table
    2. Load your XML into the XMLType table
    3. Create relational views over the XML that allow you access the content relationally
    4. (optionally) Populate your relational striuctures from the XML View using insert as select statements.

    If you reallly don't want to persist the XML in teh database fine. I've seen one customer who uises an after insert trigger and a pragma autonomious transaction procedure to populate the relational tables from the views and then rollls back the insert..

    If you reallly don't want to use XMLType then I suggust you post in the general database forums....
    mdrake-Oracle
  • 679560
    679560 Member Posts: 4
    Hi,
    Since I am not familiar with the Oracle forum and its thousands of threads, I will repost the question on another forum.
    thanks.
  • 679560
    679560 Member Posts: 4
    Moved this thread to forum Export/Import/SQL Loader & External Tables
  • mdrake-Oracle
    mdrake-Oracle Member Posts: 5,904
    edited Jan 13, 2009 11:26AM
    In case you decide to follow my advice, here's a simple example showing how to do this.. (Note the XMLTable syntax is the preferred approach in 10gr2 and later..
    SQL> spool testase.log
    SQL> --
    SQL> connect / as sysdba
    Connected.
    SQL> --
    SQL> set define on
    SQL> set timing on
    SQL> --
    SQL> define USERNAME = XDBTEST
    SQL> --
    SQL> def PASSWORD = XDBTEST
    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:00.59
    SQL> grant create any directory, drop any directory, connect, resource, alter session, create view to &USERNAME identified by &PASS
    ORD
      2  /
    old   1: grant create any directory, drop any directory, connect, resource, alter session, create view to &USERNAME identified by &
    ASSWORD
    new   1: grant create any directory, drop any directory, connect, resource, alter session, create view to XDBTEST identified by XDB
    EST
    
    Grant succeeded.
    
    Elapsed: 00:00:00.01
    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> var SCHEMAURL varchar2(256)
    SQL> var XMLSCHEMA CLOB
    SQL> --
    SQL> set define off
    SQL> --
    SQL> begin
      2    :SCHEMAURL := 'http://xmlns.example.com/askTom/TransactionList.xsd';
      3    :XMLSCHEMA :=
      4  '<?xml version="1.0" encoding="UTF-8"?>
      5  <!--W3C Schema generated by XMLSpy v2008 rel. 2 sp2 (http://www.altova.com)-->
      6  <xs:schema xmlns:xs="http://www.w3.org/2001/XMLSchema" xmlns:xdb="http://xmlns.oracle.com/xdb" xdb:storeVarrayAsTable="true">
      7     <xs:element name="TransactionList" type="transactionListType" xdb:defaultTable="LOCAL_TABLE"/>
      8     <xs:complexType name="transactionListType"  xdb:maintainDOM="false" xdb:SQLType="TRANSACTION_LIST_T">
      9             <xs:sequence>
     10                     <xs:element name="Transaction" type="transactionType" maxOccurs="unbounded" xdb:SQLCollType="TRANSACTION_V"
    >
     11             </xs:sequence>
     12     </xs:complexType>
     13     <xs:complexType name="transactionType" xdb:maintainDOM="false"  xdb:SQLType="TRANSACTION_T">
     14             <xs:sequence>
     15                     <xs:element name="TradeVersion" type="xs:integer"/>
     16                     <xs:element name="TransactionId" type="xs:integer"/>
     17                     <xs:element name="Leg" type="legType" maxOccurs="unbounded" xdb:SQLCollType="LEG_V"/>
     18             </xs:sequence>
     19             <xs:attribute name="id" type="xs:integer" use="required"/>
     20     </xs:complexType>
     21     <xs:complexType name="paymentType"  xdb:maintainDOM="false" xdb:SQLType="PAYMENT_T">
     22             <xs:sequence>
     23                     <xs:element name="StartDate" type="xs:date"/>
     24                     <xs:element name="Value" type="xs:integer"/>
     25             </xs:sequence>
     26             <xs:attribute name="id" type="xs:integer" use="required"/>
     27     </xs:complexType>
     28     <xs:complexType name="legType"  xdb:maintainDOM="false"  xdb:SQLType="LEG_T">
     29             <xs:sequence>
     30                     <xs:element name="LegNumber" type="xs:integer"/>
     31                     <xs:element name="Basis" type="xs:integer"/>
     32                     <xs:element name="FixedRate" type="xs:integer"/>
     33                     <xs:element name="Payment" type="paymentType" maxOccurs="unbounded"  xdb:SQLCollType="PAYMENT_V"/>
     34             </xs:sequence>
     35             <xs:attribute name="id" type="xs:integer" use="required"/>
     36     </xs:complexType>
     37  </xs:schema>';
     38  end;
     39  /
    
    PL/SQL procedure successfully completed.
    
    Elapsed: 00:00:00.00
    SQL> set define on
    SQL> --
    SQL> declare
      2    res boolean;
      3    xmlSchema xmlType := xmlType(:XMLSCHEMA);
      4  begin
      5    dbms_xmlschema.registerSchema
      6    (
      7      schemaurl => :schemaURL,
      8      schemadoc => xmlSchema,
      9      local     => TRUE,
     10      genTypes  => TRUE,
     11      genBean   => FALSE,
     12      genTables => TRUE,
     13      ENABLEHIERARCHY => DBMS_XMLSCHEMA.ENABLE_HIERARCHY_NONE
     14    );
     15  end;
     16  /
    
    PL/SQL procedure successfully completed.
    
    Elapsed: 00:00:00.26
    SQL> desc LOCAL_TABLE
     Name                                                                   Null?    Type
     ---------------------------------------------------------------------- -------- -----------------------------------------------
    TABLE of SYS.XMLTYPE(XMLSchema "http://xmlns.example.com/askTom/TransactionList.xsd" Element "TransactionList") STORAGE Object-rela
    ional TYPE "TRANSACTION_LIST_T"
    
    SQL> --
    SQL> create or replace VIEW TRAN_VIEW
      2  as
      3  select
      4    extractvalue(x.column_value,'/Transaction/TradeVersion/text()') tradeversion,
      5    extractvalue(x.column_value,'/Transaction//text()') transactionid
      6  from
      7    local_table,
      8    table(xmlsequence(extract(OBJECT_VALUE,'/TransactionList/Transaction'))) x
      9  /
    
    View created.
    
    Elapsed: 00:00:00.01
    SQL> create or replace VIEW TRAN_LEG_VIEW
      2  as
      3  select
      4    extractvalue(x.column_value,'/Transaction/TransactionId/text()') transactionid,
      5    extractvalue(y.column_value,'/Leg/Basis/text()') leg_basis,
      6    extractValue(y.column_value,'/Leg/FixedRate/text()') leg_fixedrate
      7  from
      8    local_table,
      9    table(xmlsequence(extract(OBJECT_VALUE,'/TransactionList/Transaction'))) x,
     10    table(xmlsequence(extract(x.column_value,'/Transaction/Leg'))) y
     11  /
    
    View created.
    
    Elapsed: 00:00:00.01
    SQL> create or replace VIEW TRAN_LEG_PAY_VIEW
      2  as
      3  select
      4    extractvalue(x.column_value,'/Transaction/TransactionId/text()') transactionid,
      5    extractvalue(y.column_value,'/Leg/LegNumber/text()') leg_legnumber,
      6    extractvalue(z.column_value,'/Payment/StartDate/text()') pay_startdate,
      7    extractValue(z.column_value,'/Payment/Value/text()') pay_value
      8  from
      9    local_table,
     10    table(xmlsequence(extract(OBJECT_VALUE,'/TransactionList/Transaction'))) x,
     11    table(xmlsequence(extract(x.column_value,'/Transaction/Leg'))) y,
     12    table(xmlsequence(extract(y.column_value,'/Leg/Payment'))) z
     13  /
    
    View created.
    
    Elapsed: 00:00:00.03
    SQL> desc TRAN_VIEW
     Name                                                                   Null?    Type
     ---------------------------------------------------------------------- -------- -----------------------------------------------
     TRADEVERSION                                                                    NUMBER(38)
     TRANSACTIONID                                                                   VARCHAR2(4000)
    
    SQL> --
    SQL> desc TRAN_LEG_VIEW
     Name                                                                   Null?    Type
     ---------------------------------------------------------------------- -------- -----------------------------------------------
     TRANSACTIONID                                                                   NUMBER(38)
     LEG_BASIS                                                                       NUMBER(38)
     LEG_FIXEDRATE                                                                   NUMBER(38)
    
    SQL> --
    SQL> desc TRAN_LEG_PAY_VIEW
     Name                                                                   Null?    Type
     ---------------------------------------------------------------------- -------- -----------------------------------------------
     TRANSACTIONID                                                                   NUMBER(38)
     LEG_LEGNUMBER                                                                   NUMBER(38)
     PAY_STARTDATE                                                                   DATE
     PAY_VALUE                                                                       NUMBER(38)
    
    SQL> --
    SQL> create or replace VIEW TRAN_VIEW_XMLTABLE
      2  as
      3  select t.*
      4    from LOCAL_TABLE,
      5         XMLTable
      6         (
      7            '/TransactionList/Transaction'
      8            passing OBJECT_VALUE
      9            columns
     10            TRADE_VERSION  NUMBER(4) path 'TradeVersion/text()',
     11            TRANSACTION_ID NUMBER(4) path 'TransactionId/text()'
     12         ) t
     13  /
    
    View created.
    
    Elapsed: 00:00:00.01
    SQL> create or replace VIEW TRAN_LEG_VIEW_XMLTABLE
      2  as
      3  select t.TRANSACTION_ID, L.*
      4    from LOCAL_TABLE,
      5         XMLTable
      6         (
      7            '/TransactionList/Transaction'
      8            passing OBJECT_VALUE
      9            columns
     10            TRANSACTION_ID NUMBER(4) path 'TransactionId/text()',
     11            LEG            XMLType   path 'Leg'
     12         ) t,
     13         XMLTABLE
     14         (
     15           '/Leg'
     16           passing LEG
     17           columns
     18           LEG_NUMBER     NUMBER(4) path 'LegNumber/text()',
     19           LEG_BASIS      NUMBER(4) path 'Basis/text()',
     20           LEG_FIXED_RATE NUMBER(4) path 'FixedRate/text()'
     21         ) l
     22  /
    
    View created.
    
    Elapsed: 00:00:00.01
    SQL> create or replace VIEW TRAN_LEG_PAY_VIEW_XMLTABLE
      2  as
      3  select TRANSACTION_ID, L.LEG_NUMBER, P.*
      4    from LOCAL_TABLE,
      5         XMLTable
      6         (
      7            '/TransactionList/Transaction'
      8            passing OBJECT_VALUE
      9            columns
     10            TRANSACTION_ID NUMBER(4) path 'TransactionId/text()',
     11            LEG            XMLType   path 'Leg'
     12         ) t,
     13         XMLTABLE
     14         (
     15           '/Leg'
     16           passing LEG
     17           columns
     18           LEG_NUMBER     NUMBER(4) path 'LegNumber/text()',
     19           PAYMENT        XMLType   path 'Payment'
     20         ) L,
     21         XMLTABLE
     22         (
     23           '/Payment'
     24           passing PAYMENT
     25           columns
     26           PAY_START_DATE     DATE      path 'StartDate/text()',
     27           PAY_VALUE          NUMBER(4) path 'Value/text()'
     28         ) p
     29  /
    
    View created.
    
    Elapsed: 00:00:00.03
    SQL> desc TRAN_VIEW_XMLTABLE
     Name                                                                   Null?    Type
     ---------------------------------------------------------------------- -------- -----------------------------------------------
     TRADE_VERSION                                                                   NUMBER(4)
     TRANSACTION_ID                                                                  NUMBER(4)
    
    SQL> --
    SQL> desc TRAN_LEG_VIEW_XMLTABLE
     Name                                                                   Null?    Type
     ---------------------------------------------------------------------- -------- -----------------------------------------------
     TRANSACTION_ID                                                                  NUMBER(4)
     LEG_NUMBER                                                                      NUMBER(4)
     LEG_BASIS                                                                       NUMBER(4)
     LEG_FIXED_RATE                                                                  NUMBER(4)
    
    SQL> --
    SQL> desc TRAN_LEG_PAY_VIEW_XMLTABLE
     Name                                                                   Null?    Type
     ---------------------------------------------------------------------- -------- -----------------------------------------------
     TRANSACTION_ID                                                                  NUMBER(4)
     LEG_NUMBER                                                                      NUMBER(4)
     PAY_START_DATE                                                                  DATE
     PAY_VALUE                                                                       NUMBER(4)
    
    SQL> --
    SQL> set long 10000 pages 100 lines 128
    SQL> set timing on
    SQL> set autotrace on explain
    SQL> set heading on feedback on
    SQL> --
    SQL> VAR DOC1 CLOB
    SQL> VAR DOC2 CLOB
    SQL> --
    SQL> begin
      2    :DOC1 :=
      3  '<TransactionList>
      4    <Transaction id="1">
      5      <TradeVersion>1</TradeVersion>
      6      <TransactionId>1</TransactionId>
      7      <Leg id="1">
      8        <LegNumber>1</LegNumber>
      9        <Basis>1</Basis>
     10        <FixedRate>1</FixedRate>
     11        <Payment id="1">
     12          <StartDate>2000-01-01</StartDate>
     13          <Value>1</Value>
     14        </Payment>
     15        <Payment id="2">
     16          <StartDate>2000-01-02</StartDate>
     17          <Value>2</Value>
     18        </Payment>
     19      </Leg>
     20      <Leg id="2">
     21        <LegNumber>2</LegNumber>
     22        <Basis>2</Basis>
     23        <FixedRate>2</FixedRate>
     24        <Payment id="1">
     25          <StartDate>2000-02-01</StartDate>
     26          <Value>10</Value>
     27        </Payment>
     28        <Payment id="2">
     29          <StartDate>2000-02-02</StartDate>
     30          <Value>20</Value>
     31        </Payment>
     32      </Leg>
     33    </Transaction>
     34    <Transaction id="2">
     35      <TradeVersion>2</TradeVersion>
     36      <TransactionId>2</TransactionId>
     37      <Leg id="1">
     38        <LegNumber>21</LegNumber>
     39        <Basis>21</Basis>
     40        <FixedRate>21</FixedRate>
     41        <Payment id="1">
     42          <StartDate>2002-01-01</StartDate>
     43          <Value>21</Value>
     44        </Payment>
     45        <Payment id="2">
     46          <StartDate>2002-01-02</StartDate>
     47          <Value>22</Value>
     48        </Payment>
     49      </Leg>
     50      <Leg id="22">
     51        <LegNumber>22</LegNumber>
     52        <Basis>22</Basis>
     53        <FixedRate>22</FixedRate>
     54        <Payment id="21">
     55          <StartDate>2002-02-01</StartDate>
     56          <Value>210</Value>
     57        </Payment>
     58        <Payment id="22">
     59          <StartDate>2002-02-02</StartDate>
     60          <Value>220</Value>
     61        </Payment>
     62      </Leg>
     63    </Transaction>
     64  </TransactionList>';
     65    :DOC2 :=
     66  '<TransactionList>
     67    <Transaction id="31">
     68      <TradeVersion>31</TradeVersion>
     69      <TransactionId>31</TransactionId>
     70      <Leg id="31">
     71        <LegNumber>31</LegNumber>
     72        <Basis>31</Basis>
     73        <FixedRate>31</FixedRate>
     74        <Payment id="31">
     75          <StartDate>3000-01-01</StartDate>
     76          <Value>31</Value>
     77        </Payment>
     78      </Leg>
     79    </Transaction>
     80  </TransactionList>';
     81  end;
     82  /
    
    PL/SQL procedure successfully completed.
    
    Elapsed: 00:00:00.01
    SQL> insert into LOCAL_TABLE values ( xmltype(:DOC1))
      2  /
    
    1 row created.
    
    Elapsed: 00:00:00.01
    
    Execution Plan
    ----------------------------------------------------------
    Plan hash value: 1
    
    ----------------------------------------------------------------------------------------
    | Id  | Operation                | Name        | Rows  | Bytes | Cost (%CPU)| Time     |
    ----------------------------------------------------------------------------------------
    |   0 | INSERT STATEMENT         |             |     1 |   100 |     1   (0)| 00:00:01 |
    |   1 |  LOAD TABLE CONVENTIONAL | LOCAL_TABLE |       |       |            |          |
    ----------------------------------------------------------------------------------------
    
    SQL> insert into LOCAL_TABLE values ( xmltype(:DOC2))
      2  /
    
    1 row created.
    
    Elapsed: 00:00:00.01
    
    Execution Plan
    ----------------------------------------------------------
    Plan hash value: 1
    
    ----------------------------------------------------------------------------------------
    | Id  | Operation                | Name        | Rows  | Bytes | Cost (%CPU)| Time     |
    ----------------------------------------------------------------------------------------
    |   0 | INSERT STATEMENT         |             |     1 |   100 |     1   (0)| 00:00:01 |
    |   1 |  LOAD TABLE CONVENTIONAL | LOCAL_TABLE |       |       |            |          |
    ----------------------------------------------------------------------------------------
    
    SQL> select * from TRAN_VIEW_XMLTABLE
      2  /
    
    TRADE_VERSION TRANSACTION_ID
    ------------- --------------
                1              1
                2              2
               31             31
    
    3 rows selected.
    
    Elapsed: 00:00:00.03
    
    Execution Plan
    ----------------------------------------------------------
    Plan hash value: 650975545
    
    -----------------------------------------------------------------------------------------------------
    | Id  | Operation          | Name                           | Rows  | Bytes | Cost (%CPU)| Time     |
    -----------------------------------------------------------------------------------------------------
    |   0 | SELECT STATEMENT   |                                |     3 |   168 |     3   (0)| 00:00:01 |
    |   1 |  NESTED LOOPS      |                                |     3 |   168 |     3   (0)| 00:00:01 |
    |*  2 |   TABLE ACCESS FULL| SYS_NTGgl+TKyhQnWoFRSrCxeX9g== |     3 |   138 |     3   (0)| 00:00:01 |
    |*  3 |   INDEX UNIQUE SCAN| SYS_C0010174                   |     1 |    10 |     0   (0)| 00:00:01 |
    -----------------------------------------------------------------------------------------------------
    
    Predicate Information (identified by operation id):
    ---------------------------------------------------
    
       2 - filter("SYS_NC_TYPEID$" IS NOT NULL)
       3 - access("NESTED_TABLE_ID"="LOCAL_TABLE"."SYS_NC0000800009$")
    
    Note
    -----
       - dynamic sampling used for this statement
    
    SQL> select * from TRAN_LEG_VIEW_XMLTABLE
      2  /
    
    TRANSACTION_ID LEG_NUMBER  LEG_BASIS LEG_FIXED_RATE
    -------------- ---------- ---------- --------------
                 1          1          1              1
                 1          2          2              2
                 2         21         21             21
                 2         22         22             22
                31         31         31             31
    
    5 rows selected.
    
    Elapsed: 00:00:00.04
    
    Execution Plan
    ----------------------------------------------------------
    Plan hash value: 1273661583
    
    ------------------------------------------------------------------------------------------------------
    | Id  | Operation           | Name                           | Rows  | Bytes | Cost (%CPU)| Time     |
    ------------------------------------------------------------------------------------------------------
    |   0 | SELECT STATEMENT    |                                |     5 |   560 |     7  (15)| 00:00:01 |
    |*  1 |  HASH JOIN          |                                |     5 |   560 |     7  (15)| 00:00:01 |
    |   2 |   NESTED LOOPS      |                                |     3 |   159 |     3   (0)| 00:00:01 |
    |*  3 |    TABLE ACCESS FULL| SYS_NTGgl+TKyhQnWoFRSrCxeX9g== |     3 |   129 |     3   (0)| 00:00:01 |
    |*  4 |    INDEX UNIQUE SCAN| SYS_C0010174                   |     1 |    10 |     0   (0)| 00:00:01 |
    |*  5 |   TABLE ACCESS FULL | SYS_NTUmyermF/S721C/2UXo40Uw== |     5 |   295 |     3   (0)| 00:00:01 |
    ------------------------------------------------------------------------------------------------------
    
    Predicate Information (identified by operation id):
    ---------------------------------------------------
    
       1 - access("SYS_ALIAS_1"."NESTED_TABLE_ID"="SYS_ALIAS_0"."SYS_NC0000800009$")
       3 - filter("SYS_NC_TYPEID$" IS NOT NULL)
       4 - access("NESTED_TABLE_ID"="LOCAL_TABLE"."SYS_NC0000800009$")
       5 - filter("SYS_NC_TYPEID$" IS NOT NULL)
    
    Note
    -----
       - dynamic sampling used for this statement
    
    SQL> select * from TRAN_LEG_PAY_VIEW_XMLTABLE
      2  /
    
    TRANSACTION_ID LEG_NUMBER PAY_START  PAY_VALUE
    -------------- ---------- --------- ----------
                 1          1 01-JAN-00          1
                 1          1 02-JAN-00          2
                 1          2 01-FEB-00         10
                 1          2 02-FEB-00         20
                 2         21 01-JAN-02         21
                 2         21 02-JAN-02         22
                 2         22 01-FEB-02        210
                 2         22 02-FEB-02        220
                31         31 01-JAN-00         31
    
    9 rows selected.
    
    Elapsed: 00:00:00.07
    
    Execution Plan
    ----------------------------------------------------------
    Plan hash value: 4004907785
    
    -------------------------------------------------------------------------------------------------------
    | Id  | Operation            | Name                           | Rows  | Bytes | Cost (%CPU)| Time     |
    -------------------------------------------------------------------------------------------------------
    |   0 | SELECT STATEMENT     |                                |     9 |  1242 |    10  (10)| 00:00:01 |
    |*  1 |  HASH JOIN           |                                |     9 |  1242 |    10  (10)| 00:00:01 |
    |*  2 |   HASH JOIN          |                                |     5 |   480 |     7  (15)| 00:00:01 |
    |   3 |    NESTED LOOPS      |                                |     3 |   159 |     3   (0)| 00:00:01 |
    |*  4 |     TABLE ACCESS FULL| SYS_NTGgl+TKyhQnWoFRSrCxeX9g== |     3 |   129 |     3   (0)| 00:00:01 |
    |*  5 |     INDEX UNIQUE SCAN| SYS_C0010174                   |     1 |    10 |     0   (0)| 00:00:01 |
    |*  6 |    TABLE ACCESS FULL | SYS_NTUmyermF/S721C/2UXo40Uw== |     5 |   215 |     3   (0)| 00:00:01 |
    |*  7 |   TABLE ACCESS FULL  | SYS_NTelW4ZRtKS+WKqCaXhsHnNQ== |     9 |   378 |     3   (0)| 00:00:01 |
    -------------------------------------------------------------------------------------------------------
    
    Predicate Information (identified by operation id):
    ---------------------------------------------------
    
       1 - access("NESTED_TABLE_ID"="SYS_ALIAS_1"."SYS_NC0000900010$")
       2 - access("SYS_ALIAS_1"."NESTED_TABLE_ID"="SYS_ALIAS_0"."SYS_NC0000800009$")
       4 - filter("SYS_NC_TYPEID$" IS NOT NULL)
       5 - access("NESTED_TABLE_ID"="LOCAL_TABLE"."SYS_NC0000800009$")
       6 - filter("SYS_NC_TYPEID$" IS NOT NULL)
       7 - filter("SYS_NC_TYPEID$" IS NOT NULL)
    
    Note
    -----
       - dynamic sampling used for this statement
    
    SQL>
    Out of interest why are you so against using XMLType...

    Edited by: mdrake on Jan 13, 2009 8:25 AM
  • Marco Gralike
    Marco Gralike Member Posts: 4,491 Silver Trophy
    Updated it here: 3216938

    Brrrroeeemmmm....

    ;-)
This discussion has been closed.