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

    XML file processing into oracle

    783602
      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
          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
            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
              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
                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
                  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
                    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
                      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
                        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
                          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
                            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
                              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
                                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
                                  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
                                    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 Previous Next