DBMS_XMLGEN question

Hi,  I am using the following code to generate a xml file:

DECLARE
  ctx DBMS_XMLGEN.ctxHandle;
  xml CLOB;
BEGIN
  ctx := dbms_xmlgen.newcontext('SELECT Col1, Col2, Col3 FROM Tab1');
  dbms_xmlgen.setnullhandling(ctx, DBMS_XMLGEN.EMPTY_TAG);
  dbms_xmlgen.setrowtag(ctx, 'ROWTAGS');
  dbms_xmlgen.setrowsettag(ctx, 'ROWSETTAGS');
  xml := dbms_xmlgen.getxml(ctx);
  dbms_output.put_line(substr(xml,1,255));
  dbms_xmlgen.closeContext(ctx);
  dbms_lob.Freetemporary(xml);
END;

This is the output generated:
<?xml version="1.0"?>
<ROWSETTAGS>
 <ROWTAGS>
  <Col1>Data1</Col1>
  <Col2>Data2</Col2>
  <Col3>Data3</Col3>
 </ROWTAGS>
 <ROWTAGS>
  <Col1>Data12</Col1>
  <Col2

I have 2 questions:

(1) How do I display all my data from the select statement? I understand that the  dbms_output.put_line statement that I am using limits the amount of data but how do I tweak it to display everything instead?

(2) I want to add a header into the XML file like this:


<?xml version="1.0"?>
<ROWSETTAGS>
<Header to be added>
 <ROWTAGS>
  <PROC_DT>20-OCT-08</PROC_DT>
  <Col1>Data1</Col1>
  <Col2>Data2</Col2>
  <Col3>Data3</Col3>
 </ROWTAGS>
 <ROWTAGS>
  <Col1>Data12</Col1>
....
 </ROWTAGS>
<ROWSETTAGS>
--end--

Note that it is a one liner only, which means it happens between <ROWSETTAGS> and <ROWTAGS> only once. How do I achieve this?  

Appreciate any help render..
AdrenalineAsked:
Who is Participating?
 
Ritesh_GargConnect With a Mentor Commented:
UTL_FILE code:
declare
     queryCtx  DBMS_XMLquery.ctxType;
     result    CLOB;
     l_length  number;
     l_text    varchar2(3000);
begin
        queryCtx := DBMS_XMLGEN.newContext(' Select  deptno,dname,cursor(select ename,sal from scott.emp where deptno= d.deptno) as emp from scott.dept d');
        result := DBMS_XMLGEN.getXML(queryCtx, DBMS_XMLGEN.NONE);
        DBMS_XMLGEN.closeContext (queryCtx);
        l_length := dbms_lob.getlength(Result);
        for i in 0..round(l_length/255) loop
            l_text    := DBMS_LOB.SUBSTR (Result, 255, 255*i + 1);
            dbms_output.put_line( l_text);
        end loop;
end;

Open in new window

0
 
leclaudeCommented:
For (1), use the SQL*Plus command "set long 50000" (or some very big  number) which will display the entire contents of the XML up to that many bytes.

I can't answer #2 though.
0
 
Ritesh_GargCommented:
Try the code below or you may use utl_file as it has a limit of more than 32000 characters.
To remove first line, you have to do substring on clob data, or use replace command.
declare
     queryCtx  DBMS_XMLquery.ctxType;
     result    CLOB;
     l_length  number;
     l_text    varchar2(3000);
begin
        queryCtx := DBMS_XMLGEN.newContext(' Select  deptno,dname,cursor(select ename,sal from scott.emp where deptno= d.deptno) as emp from scott.dept d');
        result := DBMS_XMLGEN.getXML(queryCtx, DBMS_XMLGEN.NONE);
        DBMS_XMLGEN.closeContext (queryCtx);
        l_length := dbms_lob.getlength(Result);
        for i in 0..round(l_length/255) loop
            l_text    := DBMS_LOB.SUBSTR (Result, 255, 255*i + 1);
            dbms_output.put_line( l_text);
        end loop;
end;

Open in new window

0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.