Oracle Database Discussions

Question : I do not want <ROW num="1"> in xml file I need just <row> </row>. Because I want to change it to <TRANSACTIONS> <TRANSACTION> using Replace.

Please can any one help me to remove num = "1" and num ="2" from xml file ?



--MY SPOOL SCRIPT START HERE
set serverout on
set feedback off
spool C:\emp.XML
Declare
queryCtx DBMS_XMLquery.ctxType;
result CLOB;
procedure printClobOut(result IN OUT NOCOPY CLOB) is
xmlstr varchar2(32767);
line varchar2(2000);
begin
dbms_output.enable(99999999);
xmlstr := dbms_lob.SUBSTR(result,32767);
loop
exit when xmlstr is null;
line := substr(xmlstr,1,instr(xmlstr,chr(10))-1);
dbms_output.put_line(' '||line);
xmlstr := substr(xmlstr,instr(xmlstr,chr(10))+1);
end loop;
end;
begin
-- set up the query context...!
queryCtx := DBMS_XMLQuery.newContext('select ename,job,salary from emp');
-- get the result..!

result := BMS_XMLQuery.getXML(queryCtx);
-- Now you can use the result to put it in tables/send as messages..
printClobOut(result);
DBMS_XMLQuery.closeContext(queryCtx); -- you must close the query handle..
end;
/
spool off
--MY SPOOL SCRIPT ENDS HERE

THE ABOVE WORKS AND CREATES XML FILE AS SHOWN BELOW WHICH IS GOOD


<?xml version = '1.0'?>
<ROWSET>
<ROW num="1">
<ENAME>JOHN</ENAME>
<JOB>MANAGER</JOB>
<SALARY>3000</SALARY>
</ROW>
<ROW num="2">
<ROW num="2">
<ENAME>KATTU</ENAME>
<JOB>SALESMAN</JOB>
<SALARY>1000</SALARY>
</ROW>
</ROWSET>