• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 1771
  • Last Modified:

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..
0
Adrenaline
Asked:
Adrenaline
  • 2
1 Solution
 
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

Featured Post

NEW Veeam Backup for Microsoft Office 365 1.5

With Office 365, it’s your data and your responsibility to protect it. NEW Veeam Backup for Microsoft Office 365 eliminates the risk of losing access to your Office 365 data.

  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now