1 2 Previous Next 20 Replies Latest reply: Jan 12, 2013 9:34 AM by 983173 RSS

    XMLDOM.appendChild performance

    983173
      I have to process a large xml (52,000 messages under the root element). There are 3 elements under each Message element. Example:-
      <Root>
      <Message>
      <Customer>14434</Customer>
      <MessageType>dsdf</MessageType>
      <Key>2343</Key>
      </Message>
      <Message>
      <Customer>14434</Customer>
      <MessageType>dsdf</MessageType>
      <Key>2143</Key>
      </Message>
      </Root>

      I check the 3 values against a table and append two values as follows
      <Root>
      <Message>
      <Customer>14434</Customer>
      <MessageType>dsdf</MessageType>
      <Key>2343</Key>
      <Value>7689</Value>
      <Hits>1</Hits>
      </Message>
      <Message>
      <Customer>14434</Customer>
      <MessageType>dsdf</MessageType>
      <Key>2143</Key>
      <Value>9</Value>
      <Hits>98797</Hits>
      </Message>
      </Root>

      Note: There are 58000 "Message" elements in my test

      The input arrives to the proc as a CLOB

      The message parsing and the queries to extract "Value" and "Hits" takes about 2 minutes which is acceptable.
      However, when I added two appendChild statements to add Value and Hits, the execution time shot up to 10~12 minutes.

      Any ideas on how to make it run faster?
      Is there an issue with XMLDOM.appendChild?


      DB Version: Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit

      Note:- I originally posted this query Re: XMLDOM.appendChild performance and I was asked to check in the XML DB forum.

      on the other post, I was also told that XMLType is the one I need to use in 11g but my insert to a table of XMLType from the CLOB itself takes hours.
        • 1. Re: XMLDOM.appendChild performance
          odie_63
          Thanks for reposting here.
          The message parsing and the queries to extract "Value" and "Hits" takes about 2 minutes which is acceptable.
          Could you explain how you're retrieving "Value" and "Hits" ?
          I guess that's based on the values you're parsing from the message?

          For the test case to be complete, some sample data from the table you're checking would be great.
          my insert to a table of XMLType from the CLOB itself takes hours.
          Really?

          It takes just a little over 2 sec on my local db (most of this time being taken by creating the sample doc).
          What are you doing differently?
          SQL> set timing on
          SQL> declare
            2
            3    xmldoc  clob;
            4
            5  begin
            6
            7    select xmlserialize(document
            8             xmlelement("root",
            9               xmlagg(
           10                 xmlelement("message",
           11                   xmlforest(
           12                     'A'||to_char(level, 'fm09999') as "item1"
           13                   , 'B'||to_char(level, 'fm09999') as "item2"
           14                   , 'C'||to_char(level, 'fm09999') as "item3"
           15                   )
           16                 )
           17               )
           18             )
           19             as clob
           20           )
           21    into xmldoc
           22    from dual
           23    connect by level <= 50000 ;
           24
           25    insert into tmp_xml values( xmlparse(document xmldoc) );
           26
           27  end;
           28  /
          
          PL/SQL procedure successfully completed.
          
          Elapsed: 00:00:02.17
          • 2. Re: XMLDOM.appendChild performance
            Marco Gralike
            XMLDOM...?!
            • 3. Re: XMLDOM.appendChild performance
              Marco Gralike
              Have you tried, Marc's approach as demonstrated in the SQL/PLSQL forum...?
              • 4. Re: XMLDOM.appendChild performance
                983173
                Thanks Again for the reply.
                My input is a CLOB and i say, XMLTYpe(CLOB) which takes hours.
                • 5. Re: XMLDOM.appendChild performance
                  983173
                  could you share the link to the example you are referring to?
                  • 6. Re: XMLDOM.appendChild performance
                    odie_63
                    My input is a CLOB and i say, XMLTYpe(CLOB) which takes hours.
                    How could it takes hours for you and only 2 seconds for me?
                    There's something you don't tell us.

                    Still waiting for a complete test case...

                    Thanks.
                    • 7. Re: XMLDOM.appendChild performance
                      Marco Gralike
                      Note:- I originally posted this query Re: XMLDOM.appendChild performance and I was asked to check in the XML DB forum.
                      • 8. Re: XMLDOM.appendChild performance
                        983173
                        Still waiting for a complete test case
                        will do as soon as I figure out how to attach files to this forum. :)
                        • 9. Re: XMLDOM.appendChild performance
                          odie_63
                          will do as soon as I figure out how to attach files to this forum.
                          We cannot.

                          You have to copy/paste everything in the post (between
                           tags to preserve formatting).
                          If you meant to provide the big test file you're working with, maybe it's not necessary to do so, a relevant portion of it is sufficient, we'll be able to extrapolate on our side to reach the "critical mass".
                          
                          Of course, you're still free to provide the full content via a third-party hosting website.
                          
                          Thanks.                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                            
                          • 10. Re: XMLDOM.appendChild performance
                            983173
                            README
                            1. create table TESTCLOB(DATA CLOB, HUBMSGID VARCHAR2(50));
                            2. insert z.txt (i.e. the xml) into the table with HUBMSGID = 'z';
                            3. create the proc sp_xml and run it. It takes 10~11 mins.
                            4. comment the lines 100 to 111 and run it. It takes 1~2 mins

                            sp_xml
                            create or replace
                            PROCEDURE sp_xml AS
                                /*****************************************************************************
                                process an xml
                               *****************************************************************************/
                                    TYPE ltyp_rec IS RECORD (
                                         lt_v_Customer       VARCHAR2(255),
                                         lt_v_MessageType    VARCHAR2(255),
                                         lt_v_Key            VARCHAR2(255),
                                         lt_v_Value          VARCHAR2(255),
                                         lt_n_Hits           NUMBER
                                    );
                                    
                                    ltyp_rec_data       ltyp_rec;
                             
                                    l_parser              DBMS_XMLPARSER.Parser;
                                    l_doc                 DBMS_XMLDOM.DOMDocument;
                                    
                                    l_index               INTEGER;
                                    l_totelem             INTEGER;
                                    l_dom_message_list    DBMS_XMLDOM.DOMNodeList;
                                    l_current_node           DBMS_XMLDOM.DOMNode;
                                    l_InternalKey_node    DBMS_XMLDOM.DOMNode;
                                    l_Hits_node           DBMS_XMLDOM.DOMNode;
                                    l_tmp_node            DBMS_XMLDOM.DOMNode;
                                    l_new_node            DBMS_XMLDOM.DOMNode;
                            
                                    l_new_text            DBMS_XMLDOM.DOMText;
                                    l_InternalKey_text    DBMS_XMLDOM.DOMText;
                                    l_Hits_text           DBMS_XMLDOM.DOMText;
                                    l_current_message         DBMS_XMLDOM.DOMElement;
                                    l_new_element         DBMS_XMLDOM.DOMElement;
                                    
                                    p_data            CLOB;
                                    p_clobtmp                         CLOB;
                                    
                                BEGIN
                            
                                    
                                    SELECT DATA into p_data from TESTCLOB where hubmsgid ='z';
                              
                                          --Converting the clob into XMLType
                                    l_parser := dbms_xmlparser.newParser;
                                    dbms_xmlparser.parseClob(l_parser, p_data);
                                    l_doc := dbms_xmlparser.getDocument(l_parser);
                                    dbms_xmlparser.freeParser(l_parser);
                              
                                    --***** go to the first message and then loop through subsequent messages *****--
                                    l_index:=0;
                                    l_totelem:=0;
                                    l_dom_message_list := DBMS_XMLDOM.GETELEMENTSBYTAGNAME(l_doc,'Message');
                                    l_totelem := DBMS_XMLDOM.GETLENGTH(l_dom_message_list);  
                                    dbms_output.put_line(l_totelem);
                                       
                                             -- initialize the target clobs
                                                WHILE l_index<l_totelem LOOP
                            
                                        l_current_node := DBMS_XMLDOM.ITEM(l_dom_message_list,l_index);
                                        l_current_message := DBMS_XMLDOM.MAKEELEMENT(l_current_node);
                                        l_index := l_index + 1;            
                                        BEGIN
                                              -- Extracting customerID from input XML
                                              IF DBMS_XMLDOM.ISNULL(DBMS_XMLDOM.GETELEMENTSBYTAGNAME(l_current_message,'Customer')) THEN
                                                 ltyp_rec_data.lt_v_Customer := NULL;
                                              ELSE
                                                 ltyp_rec_data.lt_v_Customer := DBMS_XMLDOM.getnodevalue(DBMS_XMLDOM.getfirstchild (DBMS_XMLDOM.ITEM(DBMS_XMLDOM.GETELEMENTSBYTAGNAME(l_current_message,'Customer'),0)));
                                                 dbms_output.put_line (' Cust : ' || ltyp_rec_data.lt_v_Customer);
                                              END IF;     
                                              -- Extracting messageType from input XML
                                              IF DBMS_XMLDOM.ISNULL(DBMS_XMLDOM.GETELEMENTSBYTAGNAME(l_current_message,'MessageType')) THEN    
                                                ltyp_rec_data.lt_v_MessageType := NULL;
                                              ELSE
                                                ltyp_rec_data.lt_v_MessageType := DBMS_XMLDOM.getnodevalue(DBMS_XMLDOM.getfirstchild (DBMS_XMLDOM.ITEM(DBMS_XMLDOM.GETELEMENTSBYTAGNAME(l_current_message,'MessageType'),0))); 
                                                dbms_output.put_line (' MessageType : ' ||  ltyp_rec_data.lt_v_MessageType);
                                              END IF;
                                              --Block to get the sequence for generating internal key, externalkey validation 
                                              --and dockeygroup to process based on the message type from the xrefset group 3705 
                            
                                              --Extracting externalKey from the input XML
                                              IF DBMS_XMLDOM.ISNULL(DBMS_XMLDOM.GETELEMENTSBYTAGNAME(l_current_message,'ExternalKey')) THEN  
                                                 ltyp_rec_data.lt_v_Key :=NULL;
                                              ELSE
                                                 ltyp_rec_data.lt_v_Key := DBMS_XMLDOM.getnodevalue(DBMS_XMLDOM.getfirstchild (DBMS_XMLDOM.ITEM(DBMS_XMLDOM.GETELEMENTSBYTAGNAME(l_current_message,'Key'),0)));
                                              END IF;
                                              
                                              --Block to Raise exceptions
                                              --COMMENTED OUT AS THIS HAS NO REASON TO INFLUENCE THE TEST CASE              
                                              --End of Block to Raise exceptions
                                              
                                              
                                              --select Value and Hits the data by checking against a table if customer, messagetype and internal key exists 
                                              --WHEN NO DATA, insert new value and set Hits to 1
                                              --Insert this into table
                                              
                                              ltyp_rec_data.lt_v_Value := '1';
                                              ltyp_rec_data.lt_n_Hits := 1;
                                              
                            
                                              --FOR FAST PERFORMANCE COMMENT THE LINE BELOW STARTING HERE               
                                              l_new_element := DBMS_XMLDOM.CREATEELEMENT(l_doc,'Value');
                                              l_new_node := xmldom.makeNode(l_new_element);
                                              l_new_text := DBMS_XMLDOM.createTextNode(l_doc,ltyp_rec_data.lt_v_Value);
                                              l_tmp_node := DBMS_XMLDOM.appendChild(l_new_node, xmldom.makeNode(l_new_text));
                                              l_tmp_node := DBMS_XMLDOM.appendChild(l_current_node, l_new_node);
                                              
                                              l_new_element := DBMS_XMLDOM.CREATEELEMENT(l_doc,'Hits');
                                              l_new_node := xmldom.makeNode(l_new_element);
                                              l_new_text := DBMS_XMLDOM.createTextNode(l_doc,to_char(ltyp_rec_data.lt_n_Hits));
                                              l_tmp_node := DBMS_XMLDOM.appendChild(l_new_node, xmldom.makeNode(l_new_text));
                                              
                                              l_tmp_node := DBMS_XMLDOM.appendChild(l_current_node, l_new_node);
                                              --FOR FAST PERFORMANCE COMMENT THE LINE BELOW ENDS HERE
                                              
                                        EXCEPTION                  
                                             --Exception handler 
                                             WHEN OTHERS THEN
                                              DBMS_OUTPUT.PUT_LINE('PROCESS EXCEPTION');
                              
                                        END;     
                                         
                                    END LOOP;
                                  
                                  DBMS_LOB.createtemporary(p_clobtmp,FALSE);
                                  DBMS_XMLDOM.writetoClob(l_doc, p_clobtmp);
                                  --DBMS_OUTPUT.put_line(p_clobtmp);
                                  dbms_xmldom.freeDocument(l_doc);
                                END sp_xml;
                            <Root>
                            <Message>
                            <Customer>DUMMY</Customer>
                            <MessageType>STORE</MessageType>
                            <Key>03616-J02</Key>
                            </Message>
                            <Message>
                            <Customer>DUMMY</Customer>
                            <MessageType>STORE</MessageType>
                            <Key>03818-J02</Key>
                            </Message>
                            <Message>
                            <Customer>DUMMY</Customer>
                            <MessageType>STORE</MessageType>
                            <Key>03827-J02</Key>
                            </Message>
                            <Message>
                            <Customer>DUMMY</Customer>
                            <MessageType>STORE</MessageType>
                            <Key>03833-J02</Key>
                            </Message>
                            <Message>
                            <Customer>DUMMY</Customer>
                            <MessageType>STORE</MessageType>
                            <Key>03845-J02</Key>
                            </Message>
                            </Root>
                            add lots of Message tags

                            Thanks
                            • 11. Re: XMLDOM.appendChild performance
                              odie_63
                              I've run the procedure - with the offending part commented out - on a 50,000-message document, it crashed my instance while running out of PGA :)

                              This procedure doesn't reflect what you're doing in reality, but thanks for the effort nonetheless.
                              The whole part about retrieving "Value" and "Hits" is missing.
                              Appending elements holding constant values is trivial and I already showed you how to do it in the original thread (with a single update statement), as well as how to parse the XML document in a more elegant and efficient way using XMLTable.

                              My intent was more like showing you how to perform the whole thing with bulk operations (XQuery combined with SQL).
                              If you still want to pursue the DOM approach and not willing to try something newer then please tell us so, I'm afraid I can't help then.

                              Edited by: odie_63 on 8 janv. 2013 01:03
                              • 12. Re: XMLDOM.appendChild performance
                                Marco Gralike
                                I am guessing that you're running out of PGA due to the "source" aka CLOB. Adding / manipulating CLOB can be very resource intensive, I have noticed, while I once used something like this to create a log file in the XDB repository (while fiddling around with an XDB event example from Mark D)

                                DBMS_XMLDOM is one of the few methods that sometimes out guns the new stuff. I wonder what would happen (although I am not "in" DBMS_XMLDOM) if the source would be an XMLType / XMLType securefile binary xml... Maybe I will try tomorrow...

                                Edited by: Marco Gralike on Jan 8, 2013 2:41 AM
                                • 13. Re: XMLDOM.appendChild performance
                                  odie_63
                                  DBMS_XMLDOM is one of the few methods that sometimes out guns the new stuff. I wonder what would happen (although I am not "in" DBMS_XMLDOM) if the source would be an XMLType / XMLType securefile binary xml... Maybe I will try tomorrow...
                                  You mean XMLType as the source of the DOM document?

                                  Tried that too on 11.2.0.3, same result.
                                  • 14. Re: XMLDOM.appendChild performance
                                    983173
                                    But from my end, the problem is that the calling application can only send a CLOB or BLOB as input.
                                    XMLTYPE(CLOB) takes long time for big messages.
                                    I thought the commented lines of code are irrelevant. All they do is select on the key values. if no datafound then get a new value from an oracle sequence. then insert a new row with the sequence and hits. if data is found, update the hits.
                                    so, in short the difference you and I see is that XMLTYPE(CLOB) takes seconds for you and for my xml file, i takes hours to complete. once we have the xmltype, i'm sure it will be simple to insert data.

                                    I have looked at your blog and you are the guru in this type of stuff and I cannot thank you enough for trying to help me but as I said step 1 i.e. converting the CLOB to XMLTYPE is a bottleneck.

                                    I will add the lines of code that do the processing and send it back to you in a bit....
                                    Adding code with all the processing bits
                                    CREATE TABLE CUSTOMERDATA 
                                    (
                                         GROUPID              INTEGER                NOT NULL, 
                                         COL01                VARCHAR2(300), 
                                         COL02                VARCHAR2(300), 
                                         COL03                VARCHAR2(300), 
                                         COL04                VARCHAR2(300), 
                                         COL05                VARCHAR2(300), 
                                         COL06                VARCHAR2(300), 
                                         COL07                VARCHAR2(300), 
                                         COL08                VARCHAR2(300), 
                                         COL09                VARCHAR2(300), 
                                         COL10                VARCHAR2(300)
                                       );
                                    
                                    CREATE SEQUENCE sq_value INCREMENT BY 1 START WITH 1 MINVALUE 1 MAXVALUE 99999999999 CYCLE CACHE 100 NOORDER;
                                    
                                    create or replace
                                    PROCEDURE sp_xml AS
                                        /*****************************************************************************
                                        process an xml
                                       *****************************************************************************/
                                            TYPE ltyp_rec IS RECORD (
                                                 lt_v_Customer       VARCHAR2(255),
                                                 lt_v_MessageType    VARCHAR2(255),
                                                 lt_v_Key            VARCHAR2(255),
                                                 lt_v_Value          VARCHAR2(255),
                                                 lt_n_Hits           NUMBER
                                            );
                                            
                                            ltyp_rec_data       ltyp_rec;
                                     
                                            l_parser              DBMS_XMLPARSER.Parser;
                                            l_doc                 DBMS_XMLDOM.DOMDocument;
                                            
                                            l_index               INTEGER;
                                            l_totelem             INTEGER;
                                            l_dom_message_list    DBMS_XMLDOM.DOMNodeList;
                                            l_current_node           DBMS_XMLDOM.DOMNode;
                                            l_InternalKey_node    DBMS_XMLDOM.DOMNode;
                                            l_Hits_node           DBMS_XMLDOM.DOMNode;
                                            l_tmp_node            DBMS_XMLDOM.DOMNode;
                                            l_new_node            DBMS_XMLDOM.DOMNode;
                                     
                                            l_new_text            DBMS_XMLDOM.DOMText;
                                            l_InternalKey_text    DBMS_XMLDOM.DOMText;
                                            l_Hits_text           DBMS_XMLDOM.DOMText;
                                            l_current_message         DBMS_XMLDOM.DOMElement;
                                            l_new_element         DBMS_XMLDOM.DOMElement;
                                            
                                            p_data            CLOB;
                                            p_clobtmp                         CLOB;
                                            
                                        BEGIN
                                     
                                            
                                            SELECT DATA into p_data from TESTCLOB where hubmsgid ='z';
                                      
                                                  --Converting the clob into XMLType
                                            l_parser := dbms_xmlparser.newParser;
                                            dbms_xmlparser.parseClob(l_parser, p_data);
                                            l_doc := dbms_xmlparser.getDocument(l_parser);
                                            dbms_xmlparser.freeParser(l_parser);
                                      
                                            --***** go to the first message and then loop through subsequent messages *****--
                                            l_index:=0;
                                            l_totelem:=0;
                                            l_dom_message_list := DBMS_XMLDOM.GETELEMENTSBYTAGNAME(l_doc,'Message');
                                            l_totelem := DBMS_XMLDOM.GETLENGTH(l_dom_message_list);  
                                            dbms_output.put_line(l_totelem);
                                               
                                                     -- initialize the target clobs
                                                        WHILE l_index<l_totelem LOOP
                                     
                                                l_current_node := DBMS_XMLDOM.ITEM(l_dom_message_list,l_index);
                                                l_current_message := DBMS_XMLDOM.MAKEELEMENT(l_current_node);
                                                l_index := l_index + 1;            
                                                BEGIN
                                                      -- Extracting customerID from input XML
                                                      IF DBMS_XMLDOM.ISNULL(DBMS_XMLDOM.GETELEMENTSBYTAGNAME(l_current_message,'Customer')) THEN
                                                         ltyp_rec_data.lt_v_Customer := NULL;
                                                      ELSE
                                                         ltyp_rec_data.lt_v_Customer := DBMS_XMLDOM.getnodevalue(DBMS_XMLDOM.getfirstchild (DBMS_XMLDOM.ITEM(DBMS_XMLDOM.GETELEMENTSBYTAGNAME(l_current_message,'Customer'),0)));
                                                         dbms_output.put_line (' Cust : ' || ltyp_rec_data.lt_v_Customer);
                                                      END IF;     
                                                      -- Extracting messageType from input XML
                                                      IF DBMS_XMLDOM.ISNULL(DBMS_XMLDOM.GETELEMENTSBYTAGNAME(l_current_message,'MessageType')) THEN    
                                                        ltyp_rec_data.lt_v_MessageType := NULL;
                                                      ELSE
                                                        ltyp_rec_data.lt_v_MessageType := DBMS_XMLDOM.getnodevalue(DBMS_XMLDOM.getfirstchild (DBMS_XMLDOM.ITEM(DBMS_XMLDOM.GETELEMENTSBYTAGNAME(l_current_message,'MessageType'),0))); 
                                                        dbms_output.put_line (' MessageType : ' ||  ltyp_rec_data.lt_v_MessageType);
                                                      END IF;
                                    
                                                      --Extracting externalKey from the input XML
                                                      IF DBMS_XMLDOM.ISNULL(DBMS_XMLDOM.GETELEMENTSBYTAGNAME(l_current_message,'ExternalKey')) THEN  
                                                         ltyp_rec_data.lt_v_Key :=NULL;
                                                      ELSE
                                                         ltyp_rec_data.lt_v_Key := DBMS_XMLDOM.getnodevalue(DBMS_XMLDOM.getfirstchild (DBMS_XMLDOM.ITEM(DBMS_XMLDOM.GETELEMENTSBYTAGNAME(l_current_message,'Key'),0)));
                                                      END IF;
                                                      
                                                      --Block to Raise exceptions
                                                      --COMMENTED OUT AS THIS HAS NO REASON TO INFLUENCE THE TEST CASE              
                                                      --End of Block to Raise exceptions
                                    
                                                        BEGIN   
                                                          SELECT COL04, to_number(COL05) 
                                                            INTO ltyp_rec_data.lt_v_Value,ltyp_rec_data.lt_n_Hits 
                                                            FROM CUSTOMERDATA 
                                                           WHERE COL01= ltyp_rec_data.lt_v_Customer
                                                             AND COL02= ltyp_rec_data.lt_v_MessageType
                                                             AND COL03=ltyp_rec_data.lt_v_Key
                                                             AND GROUPID = 10;
                                                      EXCEPTION
                                                           WHEN NO_DATA_FOUND THEN
                                                                ltyp_rec_data.lt_n_Hits        := 1;
                                                                ltyp_rec_data.lt_v_Key := NULL;
                                                      END;
                                                      --End of Extracting Hits from xrefset table based on customerID, MessageType, External Key and Groupid
                                                      IF ltyp_rec_data.lt_v_Key IS NOT NULL THEN     
                                                         DBMS_OUTPUT.put_line (' Updating .....');
                                                         ltyp_rec_data.lt_n_Hits := ltyp_rec_data.lt_n_Hits + 1;
                                                         --Update the xrefset groupid hits if the internal key is already generated for the combination of
                                                         --customerID, MessageType, External Key and Groupid 
                                                         UPDATE CUSTOMERDATA 
                                                            SET COL05 = ltyp_rec_data.lt_n_Hits,
                                                                COL06 = TO_CHAR(SYSDATE, 'YYYY-MM-DD HH24:MI:SS')  
                                                          WHERE COL02   = ltyp_rec_data.lt_v_MessageType
                                                            AND COL01   = ltyp_rec_data.lt_v_Customer
                                                            AND COL03   =ltyp_rec_data.lt_v_Key
                                                            AND GROUPID = 10;
                                                      ELSE
                                                          --Generate new value 
                                                          EXECUTE IMMEDIATE 'SELECT  sq_value.nextval FROM DUAL'
                                                             INTO ltyp_rec_data.lt_v_Key;
                                    
                                                       -- Insert new data
                                                        INSERT INTO CUSTOMERDATA 
                                                          ( GROUPID, 
                                                            COL01, 
                                                            COL02, 
                                                            COL03, 
                                                            COL04, 
                                                            COL05,
                                                            COL06) 
                                                          VALUES 
                                                          ( 10, 
                                                            ltyp_rec_data.lt_v_Customer, 
                                                            ltyp_rec_data.lt_v_MessageType, 
                                                            ltyp_rec_data.lt_v_Key, 
                                                            ltyp_rec_data.lt_v_Value, 
                                                            ltyp_rec_data.lt_n_Hits,
                                                            TO_CHAR(SYSDATE, 'YYYY-MM-DD HH24:MI:SS')
                                                          );          
                                                      END IF;
                                                        
                                                      
                                     
                                                      --FOR FAST PERFORMANCE COMMENT THE LINE BELOW STARTING HERE               
                                                     /* l_new_element := DBMS_XMLDOM.CREATEELEMENT(l_doc,'Value');
                                                      l_new_node := xmldom.makeNode(l_new_element);
                                                      l_new_text := DBMS_XMLDOM.createTextNode(l_doc,ltyp_rec_data.lt_v_Value);
                                                      l_tmp_node := DBMS_XMLDOM.appendChild(l_new_node, xmldom.makeNode(l_new_text));
                                                      l_tmp_node := DBMS_XMLDOM.appendChild(l_current_node, l_new_node);
                                                      
                                                      l_new_element := DBMS_XMLDOM.CREATEELEMENT(l_doc,'Hits');
                                                      l_new_node := xmldom.makeNode(l_new_element);
                                                      l_new_text := DBMS_XMLDOM.createTextNode(l_doc,to_char(ltyp_rec_data.lt_n_Hits));
                                                      l_tmp_node := DBMS_XMLDOM.appendChild(l_new_node, xmldom.makeNode(l_new_text));
                                                      
                                                      l_tmp_node := DBMS_XMLDOM.appendChild(l_current_node, l_new_node);*/
                                                      --FOR FAST PERFORMANCE COMMENT THE LINE BELOW ENDS HERE
                                                      
                                                EXCEPTION                  
                                                     --Exception handler 
                                                     WHEN OTHERS THEN
                                                      DBMS_OUTPUT.PUT_LINE('PROCESS EXCEPTION');
                                      
                                                END;     
                                                 
                                            END LOOP;
                                          
                                          -- DBMS_LOB.createtemporary(p_clobtmp,FALSE);
                                          --DBMS_XMLDOM.writetoClob(l_doc, p_clobtmp);
                                          --DBMS_OUTPUT.put_line(p_clobtmp);
                                          dbms_xmldom.freeDocument(l_doc);
                                        END sp_xml;
                                    Edited by: 980170 on Jan 8, 2013 3:32 AM
                                    1 2 Previous Next