8 Replies Latest reply: Jun 29, 2012 8:38 AM by 946141 RSS

    How can I merge 2 xml documents in pl/sql

    946141
      Hi,

      We are generating a report with DBMS_XMLGEN. The report is too big and the select is very cumbersome. Don't want to use Dynamic SQL. I am trying to break up the queries and generate xml and then append the docs together. Is there any way we can append one xml document to another?

      Thanks for your help in advance.
        • 1. Re: How can I merge 2 xml documents in pl/sql
          odie_63
          So you're having a performance issue?

          What's the db version? (SELECT * FROM v$version)

          How's the query passed to DBMS_XMLGEN : string, ref cursor ?
          • 2. Re: How can I merge 2 xml documents in pl/sql
            946141
            There is no performance issue. The version I am using is 11g. I am passing a string to DBMS_XMLGEN and wanted to divide the string into parts for better maintenance and readability. This is a 10 page report and the string is way too big. So I wanted to divide the string and append the parts of generated xml later. Is it possible?
            • 3. Re: How can I merge 2 xml documents in pl/sql
              odie_63
              It's possible, yes.

              You need to extract children of the root element for each XML content, and append them to a final document under a common root.
              SQL> DECLARE
                2  
                3    ctx         dbms_xmlgen.ctxHandle;
                4    tmp_xml     clob;
                5    final_xml   clob;
                6  
                7    qry1        varchar2(4000) := 'SELECT empno, ename FROM scott.emp WHERE deptno = to_number(:b_deptno)';
                8    qry2        varchar2(4000) := 'SELECT deptno, dname FROM scott.dept WHERE deptno = to_number(:b_deptno)';
                9  
               10  BEGIN
               11  
               12    dbms_lob.createtemporary(final_xml, true);
               13    final_xml := '<ROWSET>';
               14  
               15    -- extract Query #1
               16    ctx := dbms_xmlgen.newContext(qry1);
               17    dbms_xmlgen.setBindValue(ctx, 'b_deptno', '10');
               18    tmp_xml := dbms_xmlgen.getXMLType(ctx).extract('/ROWSET/*').getclobval();
               19    dbms_lob.append(final_xml, tmp_xml);
               20    dbms_xmlgen.closeContext(ctx);
               21  
               22    -- extract Query #2
               23    ctx := dbms_xmlgen.newContext(qry2);
               24    dbms_xmlgen.setBindValue(ctx, 'b_deptno', '10');
               25    tmp_xml := dbms_xmlgen.getXMLType(ctx).extract('/ROWSET/*').getclobval();
               26    dbms_lob.append(final_xml, tmp_xml);
               27    dbms_xmlgen.closeContext(ctx);
               28  
               29    -- closing the root element
               30    dbms_lob.append(final_xml, '</ROWSET>');
               31  
               32    dbms_output.put_line(final_xml);
               33  
               34    dbms_lob.freetemporary(final_xml);
               35  
               36  END;
               37  /
               
              <ROWSET><ROW>
                <EMPNO>7782</EMPNO>
                <ENAME>CLARK</ENAME>
              </ROW>
              <ROW>
                <EMPNO>7839</EMPNO>
                <ENAME>KING</ENAME>
              </ROW>
              <ROW>
                <EMPNO>7934</EMPNO>
                <ENAME>MILLER</ENAME>
              </ROW>
              <ROW>
                <DEPTNO>10</DEPTNO>
                <DNAME>ACCOUNTING</DNAME>
              </ROW>
              </ROWSET>
               
              PL/SQL procedure successfully completed
               
              You can see the overhead introduced by this approach though :
              - open and closing context handles
              - extracting fragment of the generated XML
              - append operation
              • 4. Re: How can I merge 2 xml documents in pl/sql
                Jason_(A_Non)
                What about using [url http://docs.oracle.com/cd/E11882_01/server.112/e26088/functions240.htm#SQLRF06167]XMLConcat to put the pieces together? This of course would allow you to keep them all as XMLtypes instead and you would still need to wrap them in a root node as well.
                • 5. Re: How can I merge 2 xml documents in pl/sql
                  odie_63
                  Yes, it's another way. Depends on the requirement actually, do we need to get an XMLType at the end, or is it OK to work on CLOB.
                  I also suspect XMLConcat to be less efficient, but I'll have to benchmark that.
                  • 6. Re: How can I merge 2 xml documents in pl/sql
                    946141
                    Thank you very much for the response. I really appreciate your help. My output is going to be xmltype. So I think XMLconcat might hep. I am planning to write the queries (10 queries) in this case and generate XML fragments and insert into a temp table and then concatenate them. It can also give me flexibility to add nodes as per the requirement. But I am not sure if there any limitations to this approach. Can you please let me know why do you think that XMLconcat is less efficient?
                    • 7. Re: How can I merge 2 xml documents in pl/sql
                      odie_63
                      I am planning to write the queries (10 queries) in this case and generate XML fragments and insert into a temp table and then concatenate them.
                      In this case, you'll need XMLAgg function to aggregate across rows, not XMLConcat.
                      • 8. Re: How can I merge 2 xml documents in pl/sql
                        946141
                        Thank you. That works and I really appreciate your help.