This discussion is archived
8 Replies Latest reply: Jun 29, 2012 6:38 AM by 946141 RSS

How can I merge 2 xml documents in pl/sql

946141 Newbie
Currently Being Moderated
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 Guru
    Currently Being Moderated
    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 Newbie
    Currently Being Moderated
    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 Guru
    Currently Being Moderated
    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) Expert
    Currently Being Moderated
    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 Guru
    Currently Being Moderated
    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 Newbie
    Currently Being Moderated
    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 Guru
    Currently Being Moderated
    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 Newbie
    Currently Being Moderated
    Thank you. That works and I really appreciate your help.

Legend

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