This content has been marked as final. Show 8 replies
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 ?
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?
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.
You can see the overhead introduced by this approach though :
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
- open and closing context handles
- extracting fragment of the generated XML
- append operation
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.1 person found this helpful
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.
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?
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.
Thank you. That works and I really appreciate your help.