This discussion is archived
1 2 Previous Next 20 Replies Latest reply: Jan 12, 2013 7:34 AM by 983173 RSS

XMLDOM.appendChild performance

983173 Newbie
Currently Being Moderated
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 Guru
    Currently Being Moderated
    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 Oracle ACE Director
    Currently Being Moderated
    XMLDOM...?!
  • 3. Re: XMLDOM.appendChild performance
    Marco Gralike Oracle ACE Director
    Currently Being Moderated
    Have you tried, Marc's approach as demonstrated in the SQL/PLSQL forum...?
  • 4. Re: XMLDOM.appendChild performance
    983173 Newbie
    Currently Being Moderated
    Thanks Again for the reply.
    My input is a CLOB and i say, XMLTYpe(CLOB) which takes hours.
  • 5. Re: XMLDOM.appendChild performance
    983173 Newbie
    Currently Being Moderated
    could you share the link to the example you are referring to?
  • 6. Re: XMLDOM.appendChild performance
    odie_63 Guru
    Currently Being Moderated
    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 Oracle ACE Director
    Currently Being Moderated
    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 Newbie
    Currently Being Moderated
    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 Guru
    Currently Being Moderated
    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 Newbie
    Currently Being Moderated
    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 Guru
    Currently Being Moderated
    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 Oracle ACE Director
    Currently Being Moderated
    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 Guru
    Currently Being Moderated
    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 Newbie
    Currently Being Moderated
    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

Legend

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