This discussion is archived
1 2 3 6 Previous Next 86 Replies Latest reply: Nov 19, 2010 1:57 PM by Marco Gralike RSS

XML file processing into oracle

783602 Newbie
Currently Being Moderated
Hello,

I have the following layout of xml:

<csg>
<ecrminput id="000000001">
<xml id="001" title="CustomerId">blah blah blah</xml>
<xml id="002" title="ContactId">blah blah blah</xml>
<xml id="003" title="CustomerNo">blah blah blah0</xml>
<xml id="500" title="Attribute">blah blah blah</xml>
<xml id="500" title="Attribute">blah blah blah</xml>
<xml id="1027" title="Reservation Code">blah blah blah</xml>
<xml id="1028" title="Payment Reference">blah blah blah</xml>
<xml id="1029" title="Purchaser Customer Number">blah blah blah</xml>
</ecrminput>
<ecrminput id="000000002">
<xml id="001" title="CustomerId">blah blah blah</xml>
<xml id="002" title="ContactId">blah blah blah</xml>
<xml id="500" title="Attribute">blah blah blah</xml>
<xml id="500" title="Attribute">blah blah blah</xml>
<xml id="1027" title="Reservation Code">blah blah blah</xml>
<xml id="1028" title="Payment Reference">blah blah blah</xml>
<xml id="1029" title="Purchaser Customer Number">blah blah blah</xml>
<xml id="1016" title="Call ID">blah blah blah</xml>
</ecrminput>
</csg>

and i need to find the best way to load this into oracle and extract out the node information. The solution i have come up with is to load the xml document into a table with field type of clob and then extract out using similar syntax as below:

SELECT a.*,
dbms_lob.instr(xmltype.getclobval(attribute),'>AUGUST<')
from (SELECT extractvalue(value(xml_sequence), '/ecrminput/xml[@title="CustomerId"]') CustomerId,
extract(value(xml_sequence), '/ecrminput/xml[@title="Attribute"]') Attribute
FROM xml_lfc xml_data,
table(XMLSequence(extract(XMLTYPE(xml_data), '/csg/ecrminput'))) xml_sequence) a;

This works when the file is small but i need to load and process a file of over 6,000,000 records as part of the initial load.

Any ideas? I tried going down the xmlschema route but the xml file can have different fields for each record and the <xml id="500" title="Attribute"> tag can be repeated multiple times for each record also.

Returning 1000 records using the syntax above takes approx 60 secs. I am running this on oracle 10g so am not able to use the index stuff for xmltypes.

Cheers.

Edited by: user7989950 on 07-Jul-2010 23:48

Edited by: user7989950 on 07-Jul-2010 23:53
  • 1. Re: XML file processing into oracle
    BluShadow Guru Moderator
    Currently Being Moderated
    Well if you're using 10g, you could try using the XMLTABLE syntax rather than the old table(XMLSequence .. style... e.g.
    SQL> with t as (select xmltype('<csg>
      2  <ecrminput id="000000001">
      3  <xml id="001" title="CustomerId">blah blah blah</xml>
      4  <xml id="002" title="ContactId">blah blah blah</xml>
      5  <xml id="003" title="CustomerNo">blah blah blah0</xml>
      6  <xml id="500" title="Attribute">blah blah blah</xml>
      7  <xml id="500" title="Attribute">blah blah blah</xml>
      8  <xml id="1027" title="Reservation Code">blah blah blah</xml>
      9  <xml id="1028" title="Payment Reference">blah blah blah</xml>
     10  <xml id="1029" title="Purchaser Customer Number">blah blah blah</xml>
     11  </ecrminput>
     12  <ecrminput id="000000002">
     13  <xml id="001" title="CustomerId">blah blah blah</xml>
     14  <xml id="002" title="ContactId">blah blah blah</xml>
     15  <xml id="500" title="Attribute">blah blah blah</xml>
     16  <xml id="500" title="Attribute">blah blah blah</xml>
     17  <xml id="1027" title="Reservation Code">blah blah blah</xml>
     18  <xml id="1028" title="Payment Reference">blah blah blah</xml>
     19  <xml id="1029" title="Purchaser Customer Number">blah blah blah</xml>
     20  <xml id="1016" title="Call ID">blah blah blah</xml>
     21  </ecrminput>
     22  </csg>') as xml from dual)
     23  -- END OF TEST DATA
     24  select x.ecrm_id, y.xml_id, y.title, y.xml
     25  from   t
     26        ,xmltable('/csg/ecrminput'
     27                  passing t.xml
     28                  columns ecrm_id varchar2(10) path '/ecrminput/@id'
     29                         ,xml     xmltype      path '/ecrminput/xml') x
     30        ,xmltable('/xml'
     31                  passing x.xml
     32                  columns xml_id varchar2(10) path '/xml/@id'
     33                         ,title  varchar2(20) path '/xml/@title'
     34                         ,xml    varchar2(30) path '/xml/text()') y
     35  /
    
    ECRM_ID    XML_ID     TITLE                XML
    ---------- ---------- -------------------- ------------------------------
    000000001  001        CustomerId           blah blah blah
    000000001  002        ContactId            blah blah blah
    000000001  003        CustomerNo           blah blah blah0
    000000001  500        Attribute            blah blah blah
    000000001  500        Attribute            blah blah blah
    000000001  1027       Reservation Code     blah blah blah
    000000001  1028       Payment Reference    blah blah blah
    000000001  1029       Purchaser Customer N blah blah blah
    000000002  001        CustomerId           blah blah blah
    000000002  002        ContactId            blah blah blah
    000000002  500        Attribute            blah blah blah
    000000002  500        Attribute            blah blah blah
    000000002  1027       Reservation Code     blah blah blah
    000000002  1028       Payment Reference    blah blah blah
    000000002  1029       Purchaser Customer N blah blah blah
    000000002  1016       Call ID              blah blah blah
    
    16 rows selected.
    
    SQL>
    It will take time to run on lots of data, no doubt about that.

    user7989950 wrote:
    and i need to find the best way to load this into oracle and extract out the node information. The solution i have come up with is to load the xml document into a table with field type of clob and then extract out using similar syntax as below:
    Why CLOB? Oracle supports XMLTYPE as a column datatype so that's the best one to use for storing XML.
    You may also want to utilise the XMLDB (WebDav) functionality so that the data file can just be placed in the XMLDB and read throught the resource_view e.g.
         SELECT rv.res.extract('/Resource/Contents/*')
          FROM   resource_view rv
          WHERE  lower(rv.any_path) = lower(lc_filename);
    (where lc_filename is a variable holding the filename)

    You can then insert the data directly into a table or read it into an XMLTYPE variable from the file without any need to open files, read data, close files etc.

    Another thing you can look at, if the data is large and the XMLTABLE route is too slow would be to perhaps process the XML using the DBMS_XMLDOM package, turning the XMLTYPE into a DOMDocument and recursively processing down through the nodes of that document.

    e.g.
    .
    .
        l_xml       XMLTYPE;
        l_xsdoc     DBMS_XMLDOM.DOMDocument;
        l_dn        DBMS_XMLDOM.DOMNode;
      BEGIN
        .. fetch the XML data from the file into the "l_xml" variable here ..
    
            l_xsdoc := DBMS_XMLDOM.NewDOMDocument(l_xml);
            l_dn := DBMS_XMLDOM.GetFirstChild(DBMS_XMLDOM.MakeNode(l_xsdoc)); -- first child will be the root node of the XML document
    .. etc.
  • 2. Re: XML file processing into oracle
    783602 Newbie
    Currently Being Moderated
    Hi Blushadow,

    Thanks for your advice on this. I tried using the xmltable syntax and the results seemed to be even slower. :( also tried using the xmltype as the storing field and again this seemed to have very little effect on the speed. I can get the actual data into the oracle table relatively pain free using sql loader its just extracting the nodes out thats causing me grief.

    Do you know if the DBMS_XMLDOM route will have much impact on the speed? at the moment am thinking its going to take about 4 days to process the initial file of 6 million rows.

    Do you know if the indexes available in 11g would have any effect on the speed?

    Thanks again.
  • 3. Re: XML file processing into oracle
    BluShadow Guru Moderator
    Currently Being Moderated
    user7989950 wrote:
    Hi Blushadow,

    Thanks for your advice on this. I tried using the xmltable syntax and the results seemed to be even slower. :( also tried using the xmltype as the storing field and again this seemed to have very little effect on the speed.
    Storing it in XMLTYPE won't effect the speed, but it is the correct way to store XML on the database, so I'd still advise doing that.
    I can get the actual data into the oracle table relatively pain free using sql loader its just extracting the nodes out thats causing me grief.
    Ok, if that's your preferred method, but still convert it to XMLTYPE and store it that way.
    Do you know if the DBMS_XMLDOM route will have much impact on the speed?
    Difficult to say. Your XML structure doesn't look too complicated as it only has 2 levels of nested repeating groups, so it should be as quick as it can process down it as if it were a flat file almost. I've only used the DBMS_XMLDOM package to process an XMLSchema (a fairly large and complex one) and it could do that in about 1 minute (and that included me doing a lot of table inserts and processing on top of just traversing the schema hierarchy), so I was quite happy with the performance it gave in that respect. Obviously I couldn't compare to XMLTABLE as XMLTABLE wasn't suitable for processing the schema in the way I wanted.
    at the moment am thinking its going to take about 4 days to process the initial file of 6 million rows.
    I would certainly hope that you could do 6 million rows in a much shorter time than that. Certainly on our servers I'd be expecting to process 6 million rows from XML in under 1 hour.
    Do you know if the indexes available in 11g would have any effect on the speed?
    Can't say, I don't have 11g to play with. ;)
  • 4. Re: XML file processing into oracle
    BluShadow Guru Moderator
    Currently Being Moderated
    Here's a DBMS_XMLDOM starter for you to have a play with...
    declare
      v_xml XMLTYPE := XMLTYPE('<csg>
    <ecrminput id="000000001">
    <xml id="001" title="CustomerId">blah blah blah</xml>
    <xml id="002" title="ContactId">blah blah blah</xml>
    <xml id="003" title="CustomerNo">blah blah blah0</xml>
    <xml id="500" title="Attribute">blah blah blah</xml>
    <xml id="500" title="Attribute">blah blah blah</xml>
    <xml id="1027" title="Reservation Code">blah blah blah</xml>
    <xml id="1028" title="Payment Reference">blah blah blah</xml>
    <xml id="1029" title="Purchaser Customer Number">blah blah blah</xml>
    </ecrminput>
    <ecrminput id="000000002">
    <xml id="001" title="CustomerId">blah blah blah</xml>
    <xml id="002" title="ContactId">blah blah blah</xml>
    <xml id="500" title="Attribute">blah blah blah</xml>
    <xml id="500" title="Attribute">blah blah blah</xml>
    <xml id="1027" title="Reservation Code">blah blah blah</xml>
    <xml id="1028" title="Payment Reference">blah blah blah</xml>
    <xml id="1029" title="Purchaser Customer Number">blah blah blah</xml>
    <xml id="1016" title="Call ID">blah blah blah</xml>
    </ecrminput>
    </csg>');
      v_xsdoc     DBMS_XMLDOM.DOMDocument;
      v_dn        DBMS_XMLDOM.DOMNode;
    
      PROCEDURE processnode; -- forward declaration
    
      PROCEDURE processattributes IS
        nm                      DBMS_XMLDOM.DOMNamedNodeMap;
        attr                    DBMS_XMLDOM.DOMAttr;
        attnode                 DBMS_XMLDOM.DOMNode;
        attname                 VARCHAR2(50);
        attval                  VARCHAR2(250);
      BEGIN
        nm := DBMS_XMLDOM.GetAttributes(v_dn);
        FOR i IN 0..DBMS_XMLDOM.GetLength(nm)-1
        LOOP
          attnode := DBMS_XMLDOM.Item(nm, i);
          attr := DBMS_XMLDOM.MakeAttr(attnode);
          attname := DBMS_XMLDOM.GetName(attr);
          attval := DBMS_XMLDOM.GetValue(attr);
          dbms_output.put_line('Attribute: '||attname||' Value: '||attval);
        END LOOP;
      END;
      
      PROCEDURE parse_node_element IS
        eltype        VARCHAR2(100);
      BEGIN
        eltype := DBMS_XMLDOM.GetTagName(DBMS_XMLDOM.MakeElement(v_dn));
        dbms_output.put_line('Tag: '||eltype);
        processattributes;
        processnode;
      END;
    
      PROCEDURE parse_node_text IS
      BEGIN
        DBMS_OUTPUT.PUT_LINE('Text: '||DBMS_XMLDOM.GetNodeValue(v_dn));
      END;
      PROCEDURE processnode IS
        dnl      DBMS_XMLDOM.DOMNodeList;
        nt       PLS_INTEGER;
      BEGIN
        dnl := DBMS_XMLDOM.GetChildNodes(v_dn);
        dbms_output.put_line('Child Nodes Found: '||DBMS_XMLDOM.GetLength(dnl));
        IF NOT DBMS_XMLDOM.IsNull(dnl) THEN
          FOR i IN 0 .. (DBMS_XMLDOM.GetLength(dnl) - 1)
          LOOP
            v_dn := DBMS_XMLDOM.Item(dnl, i);
            nt := DBMS_XMLDOM.GetNodeType(v_dn);
            CASE
              WHEN nt = DBMS_XMLDOM.ELEMENT_NODE THEN
                parse_node_element;
              WHEN nt IN (DBMS_XMLDOM.TEXT_NODE) THEN
                parse_node_text();
            ELSE
              NULL; -- Unhandled node type;
            END CASE;
          END LOOP;
        END IF;
      END;
    BEGIN
      v_xsdoc := DBMS_XMLDOM.NewDOMDocument(v_xml);
      v_dn := DBMS_XMLDOM.GetFirstChild(DBMS_XMLDOM.MakeNode(v_xsdoc)); -- <csg>
      processnode;
    END;
    which is currently outputting like this...
    Child Nodes Found: 2
    Tag: ecrminput
    Attribute: id Value: 000000001
    Child Nodes Found: 8
    Tag: xml
    Attribute: id Value: 001
    Attribute: title Value: CustomerId
    Child Nodes Found: 1
    Text: blah blah blah
    Tag: xml
    Attribute: id Value: 002
    Attribute: title Value: ContactId
    Child Nodes Found: 1
    Text: blah blah blah
    Tag: xml
    Attribute: id Value: 003
    Attribute: title Value: CustomerNo
    Child Nodes Found: 1
    Text: blah blah blah0
    Tag: xml
    Attribute: id Value: 500
    Attribute: title Value: Attribute
    Child Nodes Found: 1
    Text: blah blah blah
    Tag: xml
    Attribute: id Value: 500
    Attribute: title Value: Attribute
    Child Nodes Found: 1
    Text: blah blah blah
    Tag: xml
    Attribute: id Value: 1027
    Attribute: title Value: Reservation Code
    Child Nodes Found: 1
    Text: blah blah blah
    Tag: xml
    Attribute: id Value: 1028
    Attribute: title Value: Payment Reference
    Child Nodes Found: 1
    Text: blah blah blah
    Tag: xml
    Attribute: id Value: 1029
    Attribute: title Value: Purchaser Customer Number
    Child Nodes Found: 1
    Text: blah blah blah
    Tag: ecrminput
    Attribute: id Value: 000000002
    Child Nodes Found: 8
    Tag: xml
    Attribute: id Value: 001
    Attribute: title Value: CustomerId
    Child Nodes Found: 1
    Text: blah blah blah
    Tag: xml
    Attribute: id Value: 002
    Attribute: title Value: ContactId
    Child Nodes Found: 1
    Text: blah blah blah
    Tag: xml
    Attribute: id Value: 500
    Attribute: title Value: Attribute
    Child Nodes Found: 1
    Text: blah blah blah
    Tag: xml
    Attribute: id Value: 500
    Attribute: title Value: Attribute
    Child Nodes Found: 1
    Text: blah blah blah
    Tag: xml
    Attribute: id Value: 1027
    Attribute: title Value: Reservation Code
    Child Nodes Found: 1
    Text: blah blah blah
    Tag: xml
    Attribute: id Value: 1028
    Attribute: title Value: Payment Reference
    Child Nodes Found: 1
    Text: blah blah blah
    Tag: xml
    Attribute: id Value: 1029
    Attribute: title Value: Purchaser Customer Number
    Child Nodes Found: 1
    Text: blah blah blah
    Tag: xml
    Attribute: id Value: 1016
    Attribute: title Value: Call ID
    Child Nodes Found: 1
    Text: blah blah blah
    
    PL/SQL procedure successfully completed.
    
    SQL>
  • 5. Re: XML file processing into oracle
    783602 Newbie
    Currently Being Moderated
    Hi BluShadow,

    Thanks for all your help on this.

    Will give the DBMS_XMLDOM a go and see how it compares to the straight extract from the xmltype. I had an xml file in the xmltype field and using xmltable and extract value it took 50 seconds to extract and insert 950 records which seems rather slow!

    Will let you know how DBMS_XMLDOM compares.

    Thanks again.
  • 6. Re: XML file processing into oracle
    783602 Newbie
    Currently Being Moderated
    Hello,

    I have tried both methods now and with an xml document of 1880 rows the xmltable route takes 5 mins to split out the records and insert and the DBMS_XMLDOM takes even longer to extract all the node information. Any ideas why this may be taking such a long time??

    The plan is indicating that there is a COLLECTION ITERATOR PICKLER FETCH going on which i have read is not a particularly good thing but am not sure how to get round this??

    Any ideas would be gratefully received??

    Cheers.
  • 7. Re: XML file processing into oracle
    mdrake Expert
    Currently Being Moderated
    The correct answer to this problem is to define an XML Schema that describes the data, register the XML schema with the Database for Ojbect Relational stoage (ensuring DOM Fidelity is disabled, and storeVarrayAsTable=true (in Pre 11.1.x) ). Insert the XML file into the table and run XMLTable operators to get the results. Check that the explain plans sfor the XML Table operations show no COLLECTION ITERATOR PICKLER FETCH.

    The basic idea behind this is covered in the XML DB basic dmeo on the OTN page.
  • 8. Re: XML file processing into oracle
    mdrake Expert
    Currently Being Moderated
    Eg
    SQL> spool testcase.log
    SQL> --
    SQL> connect / as sysdba
    Connected.
    SQL> --
    SQL> set define on
    SQL> set timing on
    SQL> --
    SQL> def USERNAME = OTN
    SQL> --
    SQL> def PASSWORD = &USERNAME
    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 OTN cascade
    
    User dropped.
    
    Elapsed: 00:00:13.22
    SQL> grant create any directory, drop any directory, connect, resource, alter session, create view to &USERNAME identified by &PASSWORD
      2  /
    old   1: grant create any directory, drop any directory, connect, resource, alter session, create view to &USERNAME identified by &PASSWORD
    new   1: grant create any directory, drop any directory, connect, resource, alter session, create view to OTN identified by OTN
    
    Grant succeeded.
    
    Elapsed: 00:00:00.15
    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 OTN default tablespace USERS temporary tablespace TEMP
    
    User altered.
    
    Elapsed: 00:00:00.04
    SQL> connect &USERNAME/&PASSWORD
    Connected.
    SQL> --
    SQL> -- create or replace directory XMLDIR as '&XMLDIR'
    SQL> -- /
    SQL> var SCHEMAURL    varchar2(256)
    SQL> var XMLSCHEMA    varchar2(4000)
    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.03
    SQL>
    SQL> begin
      2    :SCHEMAURL:= 'http://xmlns.example.org/xsd/testcase.xsd';
      3    :XMLSCHEMA :=
      4  '<?xml version="1.0" encoding="UTF-8"?>
      5  <!--W3C Schema generated by XMLSpy v2010 rel. 2 (x64) (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="csg" type="csgType" xdb:defaultTable="CSG_TABLE"/>
      8     <xs:complexType name="ecrminputType" xdb:maintainDOM="false">
      9             <xs:sequence>
     10                     <xs:element name="xml" type="xmlType" maxOccurs="unbounded"/>
     11             </xs:sequence>
     12             <xs:attribute name="id" type="xs:byte" use="required"/>
     13     </xs:complexType>
     14     <xs:complexType name="xmlType"  xdb:maintainDOM="false">
     15             <xs:simpleContent>
     16                     <xs:extension base="xs:string">
     17                             <xs:attribute name="title" type="xs:string" use="required"/>
     18                             <xs:attribute name="id" type="xs:short" use="required"/>
     19                     </xs:extension>
     20             </xs:simpleContent>
     21     </xs:complexType>
     22     <xs:complexType name="csgType"  xdb:maintainDOM="false">
     23             <xs:sequence>
     24                     <xs:element name="ecrminput" type="ecrminputType" maxOccurs="unbounded"/>
     25             </xs:sequence>
     26     </xs:complexType>
     27  </xs:schema>';
     28    :INSTANCE :=
     29  '<csg>
     30     <ecrminput id="000000001">
     31             <xml id="001" title="CustomerId">blah blah blah</xml>
     32             <xml id="002" title="ContactId">blah blah blah</xml>
     33             <xml id="003" title="CustomerNo">blah blah blah0</xml>
     34             <xml id="500" title="Attribute">blah blah blah</xml>
     35             <xml id="500" title="Attribute">blah blah blah</xml>
     36             <xml id="1027" title="Reservation Code">blah blah blah</xml>
     37             <xml id="1028" title="Payment Reference">blah blah blah</xml>
     38             <xml id="1029" title="Purchaser Customer Number">blah blah blah</xml>
     39     </ecrminput>
     40     <ecrminput id="000000002">
     41             <xml id="001" title="CustomerId">blah blah blah</xml>
     42             <xml id="002" title="ContactId">blah blah blah</xml>
     43             <xml id="500" title="Attribute">blah blah blah</xml>
     44             <xml id="500" title="Attribute">blah blah blah</xml>
     45             <xml id="1027" title="Reservation Code">blah blah blah</xml>
     46             <xml id="1028" title="Payment Reference">blah blah blah</xml>
     47             <xml id="1029" title="Purchaser Customer Number">blah blah blah</xml>
     48             <xml id="1016" title="Call ID">blah blah blah</xml>
     49     </ecrminput>
     50  </csg>';
     51  end;
     52  /
    
    PL/SQL procedure successfully completed.
    
    Elapsed: 00:00:00.04
    SQL> begin
      2    dbms_xmlschema.registerSchema
      3    (
      4      schemaurl       => :SCHEMAURL,
      5      schemadoc       => :XMLSCHEMA,
      6      local           => TRUE,
      7      genTypes        => TRUE,
      8      genBean         => FALSE,
      9      genTables       => TRUE
     10     ,ENABLEHIERARCHY => DBMS_XMLSCHEMA.ENABLE_HIERARCHY_NONE
     11    );
     12  end;
     13  /
    
    PL/SQL procedure successfully completed.
    
    Elapsed: 00:00:23.69
    SQL> DESC CSG_TABLE
     Name                                                                   Null?    Type
     ---------------------------------------------------------------------- -------- -----------------------------------------------
    TABLE of SYS.XMLTYPE(XMLSchema "http://xmlns.example.org/xsd/testcase.xsd" Element "csg") STORAGE Object-relational TYPE "csgType1008_T"
    
    SQL> --
    SQL> insert into CSG_TABLE values (XMLTYPE(:INSTANCE))
      2  /
    
    1 row created.
    
    Elapsed: 00:00:00.82
    SQL> commit
      2  /
    
    Commit complete.
    
    Elapsed: 00:00:00.01
    SQL> set autotrace on explain
    SQL> set pages 60 lines 128 heading on
    SQL> --
    SQL> select eid, id, title
      2    from CSG_TABLE,
      3         XMLTABLE
      4         (
      5           '/csg/ecrminput'
      6           passing OBJECT_VALUE
      7           columns
      8           eid path '@id',
      9           xml XMLTYPE path 'xml'
     10         ) ec,
     11         XMLTABLE
     12         (
     13           '/xml'
     14           passing ec.xml
     15           columns
     16           ID path '@id',
     17           TITLE VARCHAR2(64) path '@title'
     18        )
     19  /
    
           EID         ID TITLE
    ---------- ---------- ----------------------------------------------------------------
             1          1 CustomerId
             1          2 ContactId
             1          3 CustomerNo
             1        500 Attribute
             1        500 Attribute
             1       1027 Reservation Code
             1       1028 Payment Reference
             1       1029 Purchaser Customer Number
             2          1 CustomerId
             2          2 ContactId
             2        500 Attribute
             2        500 Attribute
             2       1027 Reservation Code
             2       1028 Payment Reference
             2       1029 Purchaser Customer Number
             2       1016 Call ID
    
    16 rows selected.
    
    Elapsed: 00:00:00.17
    
    Execution Plan
    ----------------------------------------------------------
    Plan hash value: 1366306143
    
    ------------------------------------------------------------------------------------------------------
    | Id  | Operation           | Name                           | Rows  | Bytes | Cost (%CPU)| Time     |
    ------------------------------------------------------------------------------------------------------
    |   0 | SELECT STATEMENT    |                                |    16 | 33088 |     7  (15)| 00:00:01 |
    |*  1 |  HASH JOIN          |                                |    16 | 33088 |     7  (15)| 00:00:01 |
    |   2 |   NESTED LOOPS      |                                |     2 |    86 |     3   (0)| 00:00:01 |
    |   3 |    TABLE ACCESS FULL| SYS_NTibP98R9kTn2YiNq8xGn8YQ== |     2 |    66 |     3   (0)| 00:00:01 |
    |*  4 |    INDEX UNIQUE SCAN| SYS_C0011304                   |     1 |    10 |     0   (0)| 00:00:01 |
    |   5 |   TABLE ACCESS FULL | SYS_NTD2gZEKF8S0WERcCT1rMcqg== |    16 | 32400 |     3   (0)| 00:00:01 |
    ------------------------------------------------------------------------------------------------------
    
    Predicate Information (identified by operation id):
    ---------------------------------------------------
    
       1 - access("SYS_ALIAS_1"."NESTED_TABLE_ID"="SYS_ALIAS_0"."SYS_NC0000600007$")
       4 - access("NESTED_TABLE_ID"="CSG_TABLE"."SYS_NC0000800009$")
    
    Note
    -----
       - dynamic sampling used for this statement (level=2)
    
    SQL>
  • 9. Re: XML file processing into oracle
    mdrake Expert
    Currently Being Moderated
    Adding the Value
    SQL> set pages 60 lines 164 heading on
    SQL> --
    SQL> select eid, id, title, value
      2    from CSG_TABLE,
      3         XMLTABLE
      4         (
      5           '/csg/ecrminput'
      6           passing OBJECT_VALUE
      7           columns
      8           eid path '@id',
      9           xml XMLTYPE path 'xml'
     10         ) ec,
     11         XMLTABLE
     12         (
     13           '/xml'
     14           passing ec.xml
     15           columns
     16           ID path '@id',
     17           TITLE VARCHAR2(64) path '@title',
     18           VALUE VARCHAR2(64) path '.'
     19        )
     20  /
    
           EID         ID TITLE                                                            VALUE
    ---------- ---------- ---------------------------------------------------------------- ----------------------------------------------------------------
             1          1 CustomerId                                                       blah blah blah
             1          2 ContactId                                                        blah blah blah
             1          3 CustomerNo                                                       blah blah blah0
             1        500 Attribute                                                        blah blah blah
             1        500 Attribute                                                        blah blah blah
             1       1027 Reservation Code                                                 blah blah blah
             1       1028 Payment Reference                                                blah blah blah
             1       1029 Purchaser Customer Number                                        blah blah blah
             2          1 CustomerId                                                       blah blah blah
             2          2 ContactId                                                        blah blah blah
             2        500 Attribute                                                        blah blah blah
             2        500 Attribute                                                        blah blah blah
             2       1027 Reservation Code                                                 blah blah blah
             2       1028 Payment Reference                                                blah blah blah
             2       1029 Purchaser Customer Number                                        blah blah blah
             2       1016 Call ID                                                          blah blah blah
    
    16 rows selected.
    
    Elapsed: 00:00:00.07
    
    Execution Plan
    ----------------------------------------------------------
    Plan hash value: 1366306143
    
    ------------------------------------------------------------------------------------------------------
    | Id  | Operation           | Name                           | Rows  | Bytes | Cost (%CPU)| Time     |
    ------------------------------------------------------------------------------------------------------
    |   0 | SELECT STATEMENT    |                                |    16 | 65120 |     7  (15)| 00:00:01 |
    |*  1 |  HASH JOIN          |                                |    16 | 65120 |     7  (15)| 00:00:01 |
    |   2 |   NESTED LOOPS      |                                |     2 |    86 |     3   (0)| 00:00:01 |
    |   3 |    TABLE ACCESS FULL| SYS_NTibP98R9kTn2YiNq8xGn8YQ== |     2 |    66 |     3   (0)| 00:00:01 |
    |*  4 |    INDEX UNIQUE SCAN| SYS_C0011304                   |     1 |    10 |     0   (0)| 00:00:01 |
    |   5 |   TABLE ACCESS FULL | SYS_NTD2gZEKF8S0WERcCT1rMcqg== |    16 | 64432 |     3   (0)| 00:00:01 |
    ------------------------------------------------------------------------------------------------------
    
    Predicate Information (identified by operation id):
    ---------------------------------------------------
    
       1 - access("SYS_ALIAS_1"."NESTED_TABLE_ID"="SYS_ALIAS_0"."SYS_NC0000600007$")
       4 - access("NESTED_TABLE_ID"="CSG_TABLE"."SYS_NC0000800009$")
    
    Note
    -----
       - dynamic sampling used for this statement (level=2)
    
    SQL>
  • 10. Re: XML file processing into oracle
    BluShadow Guru Moderator
    Currently Being Moderated
    Nice one Mark. I've not done it that way before, but have bookmarked this thread for future reference and will have a play with it myself to familiarise. ;)
  • 11. Re: XML file processing into oracle
    783602 Newbie
    Currently Being Moderated
    Thanks for your help on this mark and Blushadow. I did look into creating an xsd and registering it but not sure i fully understood the reason for doing this. Will give this a go and see how it turns out. I appologise now for any further simple questions :)

    Cheers.
  • 12. Re: XML file processing into oracle
    BluShadow Guru Moderator
    Currently Being Moderated
    Well I've had a go myself with some of my own test data and I'm happy with it.

    Tested on 10.2.0.1, it still uses the COLLECTION ITERATOR PICKLER FETCH
    Tested on 10.2.0.3, it no longer uses that and is more performant.

    ;)
  • 13. Re: XML file processing into oracle
    783602 Newbie
    Currently Being Moderated
    I have now tried this on the same data and the same sql returns all the results in under 3 seconds. Thanks again to both of you. :)

    Cheers.
  • 14. Re: XML file processing into oracle
    783602 Newbie
    Currently Being Moderated
    Hello again,

    I have tried this and as mentioned it works really well. one question though is when i register the schema and explicitly give it a defaulttable is there anyway i can also explicitily define the column name of xmltype as well?

    I need this as i am loading in the xml via sql loader and need to reference the field name in the control file.

    Cheers.
1 2 3 6 Previous Next