7 Replies Latest reply: Mar 20, 2013 1:32 PM by 937454 RSS

    handling xml doc better method

    937454
      Hi,
      My db version: Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - 64bit Production
      I have two methods of handling xml
      METHOD 1
      Insert input xmldoc into a global temp table and perform Insert/Update/Delete using the temp table, like below
      insCtx := DBMS_XMLStore.newContext ('gtt_xmlTempTbl');
      rows := DBMS_XMLStore.insertXML (insCtx, p_xmlDoc);
      DBMS_XMLStore.closeContext (insCtx);
      
      INSERT INTO tableA
          SELECT * from gtt_xmltemptbl;
      
      Sample xml doc is:
      <ROWSET>
          <ROW>
              <MODEL>ABC</MODEL>
              <APP>APP1</APP>
              <PARAM>P1</PARAM>
              <VALUE>V1</VALUE>
              </ROW>
              <ROW>
              <MODEL>ABC</MODEL>
              <APP>APP1</APP>
              <PARAM>P2</PARAM>
              <VALUE>V2</VALUE>
          </ROW>
      </ROWSET>
      METHOD 2
      MERGE INTO tableA
      USING
      SELECT param                              
      FROM XMLTABLE (                                 
          '/ROWSET/ROW/PARAMS/PARAM'      
          PASSING XMLPARSE (DOCUMENT p_xmldoc)        
          COLUMNS PARAM VARCHAR2(30) PATH '.')) s                                                                               
      ON (pe.model = tableA.model                              
          AND pe.app = tableA.app)         
      WHEN NOT MATCHED THEN INSERT                        
          (....)
      WHEN MATCHED THEN  UPDATE (...) 
      Sample xml doc is:
      <ROWSET>
          <ROW>
              <MODEL>ABC</MODEL>
              <APP>APP1</APP>
              <PARAMS>
                  <PARAM>P1</PARAM>
                  <VALUE>V1</VALUE>
                  <PARAM>P2</PARAM>
                  <VALUE>V2</VALUE>
                  <PARAM>P3</PARAM>
                  <VALUE>V2</VALUE>
              </PARAMS>            
          </ROW>
      </ROWSET>
      Please advice which method is better approach.
      The xml can be huge, can be like 3000-5000 param values.
      The above code is not 100% accurate, but hope it gives the idea.
        • 1. Re: handling xml doc better method
          odie_63
          Please advice which method is better approach.
          Define "better".

          Better performance, memory usage ?

          Did you test both methods?
          What are your conclusions?
          Which method satisfies you the most?
          The xml can be huge, can be like 3000-5000 param values.
          5000 <PARAM>/<VALUE> pairs or 5000 <ROW>?

          Do you really have two different XML structures to deal with?

          If you could provide a better test case, relatively close to the reality, then I could show you how I'd do it.

          Thanks.
          • 2. Re: handling xml doc better method
            937454
            Hi Odie,
            Thank you for responding.
            Below are answers for some of your questions.
            Better performance, memory usage ?
            Better performance is the criteria.
            Did you test both methods?
            What are your conclusions?
            Which method satisfies you the most?
            Yes, I used both the methods in my code.

            I realize with the first method, the xml format has to be same as above, any variation and it wouldnt work. Please correct me if I am wrong. I tested the first method with different xml and as expected got xml parsing errors. And also the temptable columns keep increasing, as we see more n more elements are added to the xmldata
            But with the first method, coding is easy, ones I dump the xml data into a temp table, I can do any manipulation with it. Without having to parse the xmldoc again n again.

            With the second method, it is flexible with any xml format. But for each distinct element in the xmldoc, I have to extract the value. This makes coding a bit complex, especially when I have to make multiple joins with several tables, compare and do all kinds of manipulations. I could be wrong, but thats what appears to me when I code using second method.

            Both the methods work best for me.
            I want to understand which method works best in a real-time scenario, from performance stand point, when 1000's of concurrent users are accesing the tables and sending xml feeds.
            The xml can be huge, can be like 3000-5000 param values.
            5000 <PARAM>/<VALUE> pairs or 5000 <ROW>?
            
            Do you really have two different XML structures to deal with?
            Yes, 5000 param and value pairs.

            Since we are in the development phase, we used both formats. But now we have to decide on one format.
            My above answers could be vague.
            Please let me know if more information is required.

            Edited by: 934451 on Mar 13, 2013 8:25 AM
            • 3. Re: handling xml doc better method
              odie_63
              But now we have to decide on one format.
              Assuming the target relational table ("tableA" in your example) has a structure similar to this :
              CREATE TABLE tableA (
                MODEL  VARCHAR2(30), --PK
                APP VARCHAR2(30),  --PK
                PARAM  VARCHAR2(30), 
                VALUE VARCHAR2(30)
              )
              then I would choose the first format, regardless of the method.

              The second format stores each PARAM/VALUE pair as siblings under a common parent which makes it quite unfriendly to be parsed efficiently.
              Speaking of which, I wonder what query you used to parse this specific format, certainly not the one you've shown I guess.

              Better performance is the criteria.
              Parsing 5000 logical records efficiently will require (at least) a temporary storage of the XML document in a XMLType column (with Binary XML option).
              You'll find numerous (and recent) examples of this approach in this forum and in the {forum:id=34} forum as well.
              • 4. Re: handling xml doc better method
                937454
                Thanks Odie for your inputs.
                • 5. Re: handling xml doc better method
                  937454
                  I observed this and made me doubtful
                  Speaking of which, I wonder what query you used to parse this specific format, certainly not the one you've shown I guess.
                  Actually I used what I have show ..something in those lines. Is there a problem with that???
                  • 6. Re: handling xml doc better method
                    odie_63
                    Speaking of which, I wonder what query you used to parse this specific format, certainly not the one you've shown I guess.
                    Actually I used what I have show ..something in those lines. Is there a problem with that???
                    In your first post you said :
                    Sample xml doc is:
                    <ROWSET>
                        <ROW>
                            <MODEL>ABC</MODEL>
                            <APP>APP1</APP>
                            <PARAMS>
                                <PARAM>P1</PARAM>
                                <VALUE>V1</VALUE>
                                <PARAM>P2</PARAM>
                                <VALUE>V2</VALUE>
                                <PARAM>P3</PARAM>
                                <VALUE>V2</VALUE>
                            </PARAMS>            
                        </ROW>
                    </ROWSET>
                    and that you used this to parse it :
                    FROM XMLTABLE (                                 
                        '/ROWSET/ROW/PARAMS/PARAM'      
                        PASSING XMLPARSE (DOCUMENT p_xmldoc)        
                        COLUMNS PARAM VARCHAR2(30) PATH '.')
                    As that query can only extract PARAM elements, I was just wondering how you were retrieving the corresponding VALUE elements ?
                    Did you use another query to get them ?
                    • 7. Re: handling xml doc better method
                      937454
                      Oh I get it now. Yes I dint provide the right code. Sorry my bad.
                      The xml I get is sth like this
                          xmldoc CLOB := '<?xml version="1.0"?>
                      <ROWSET>
                      <ROW>
                      <PARAMETERS>
                        <PARAMETER>
                          <NAME>oo</NAME>
                          <VALUE>12</VALUE>
                        </PARAMETER>
                        <PARAMETER>
                          <NAME>pp</NAME>
                          <VALUE>12</VALUE>
                        </PARAMETER>
                        <PARAMETER>
                          <NAME>xx</NAME>
                          <VALUE>12</VALUE>
                        </PARAMETER>  
                      </PARAMETERS>
                      <BUSINESSGROUPS>
                        <BUSINESSGROUP>RETAIL</BUSINESSGROUP>
                        <BUSINESSGROUP>RETAILTIP</BUSINESSGROUP>
                      </BUSINESSGROUPS>
                      <ENTITLEMENTS>
                        <ENTITLEMENT>AMEX</ENTITLEMENT>
                      </ENTITLEMENTS>
                      <DEFAULTS>
                        <DEFAULTNAME>DEF5</DEFAULTNAME>
                      </DEFAULTS>
                      </ROW>
                      </ROWSET>'
                      And I handle name/value pairs, sth in these lines
                      MERGE INTO tab6 tp                      
                      USING (                                             
                          WITH t AS (                                     
                           SELECT name                                 
                               ,value                                  
                               FROM XMLTABLE (                         
                                '/ROWSET/ROW/PARAMETERS/PARAMETER'  
                                PASSING XMLPARSE (DOCUMENT p_xmldoc)
                                COLUMNS NAME VARCHAR2(30) PATH 'NAME'                                                                               
                                    ,VALUE VARCHAR2(500) PATH 'VALUE'))                                                                             
                      
                           SELECT ap.col1                     
                               ,t.col2                                
                               ,ap.col3                           
                               ,ap.col4                         
                           FROM tab1 ap                     
                               ,t                                      
                               , tab3 gd                       
                           WHERE ap.pk1 = gd.pk1             
                               AND ap.pk2 = gd.pk2         
                               AND ap.pk3 = gd.pk3     
                               AND ap.pk4 = gd.pk4       
                               AND ap.pk5 = t.name) s         
                      ON (tp.pk1 = v_dldid                              
                          AND tp.pk2 = s.col1)         
                      WHEN MATCHED THEN UPDATE                            
                      SET tp.col = s.col2                         
                          ,tp.modifiedby = p_user                         
                          ,tp.modifieddate = SYSDATE                      
                      WHEN NOT MATCHED THEN INSERT (....);