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?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

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
Ritesh_GargCommented:
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

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Oracle Database

From novice to tech pro — start learning today.