Generating XML file using Oracle Pl/sql

http://www.akadia.com/services/ora_gen_xml.html

How do you want it to look for all 20K rows?

You will still need a master grouping node like ALL_ROWS.

Check out the following:

drop table tab1 purge;
create table tab1( sctr_id number, azon_cd number, last_updt_user_id varchar2(10));

insert into tab1 values(7901,859,'MARIO');
insert into tab1 values(1111,222,'FRED');
commit;

select xmlelement("ALL_ROWS",
	xmlagg(
		xmlelement("RESULT",
			xmlforest(sctr_id as "SCTR_ID", azon_cd as "Azon_CD",last_updt_user_id as "LAST_UPDT_USER_ID")
		)
	)
)
from tab1;

Select allOpen in new window

Hi my procedure looks like this but its not returnong all values..

declare
   PATH varchar2(500) :='/tmp';
   v_out  VARCHAR2(500);
   v_file UTL_FILE.FILE_TYPE;
   ctxXMLContext DBMS_XMLGEN.ctxHandle;
   cXmlText CLOB;
   rc sys_refcursor;
   v_more  BOOLEAN := TRUE;

    BEGIN
        OPEN rc FOR
       
        select SCTR_SIMU_ID,AVON_CD,LAST_UPDT_USER_ID from sctr_simu where rownum<3001;
       
       
       ctxXMLContext := DBMS_XMLGEN.NEWCONTEXT(rc);

       DBMS_XMLGEN.SETROWTAG(ctxXMLContext, 'RESULT'); -- defaults to ROW

       cXmlText := DBMS_XMLGEN.GETXML(ctxXMLContext);
      --   DBMS_OUTPUT.PUT_LINE(cXmlText);
       v_file := UTL_FILE.fopen (PATH, 'BankSlip.xml', 'w');
   
    WHILE v_more
        LOOP
        UTL_FILE.put(v_file,Substr(cXmlText, 1, 32767) );
            IF LENGTH(cXmlText) > 32767 THEN
                cXmlText :=  SUBSTR(cXmlText, 32768);
                DBMS_OUTPUT.PUT_LINE(cXmlText);
            ELSE
                v_more := FALSE;
                DBMS_OUTPUT.PUT_LINE(cXmlText);
            END IF;
        END LOOP;
       -- utl_file.put_line(v_file,cXmlText);
    UTL_FILE.fclose(v_file);
    EXCEPTION
    WHEN OTHERS
    THEN
      v_out := SQLERRM;
      dbms_output.put_line('-------- '||v_out);
      UTL_FILE.fclose (v_file);
END;


It just returns 220 records..

Personally, I don't like DBMS_XMLGEN.

Have you tried the SQL I posted?  You can return the result as a single CLOB.  No cursor loop necessary.

okay , in that case, how should i generate the XML for the whole table.
it contains 20 k records.
you have inserted it in a temp table and then returning it as clob.
how should i do this without temp table?

>>you have inserted it in a temp table and then returning it as clob.

I created a test table to mimic your sample so I could provide a working example.

To use my SQL, just replace tab1 with your table name in my SQL.

select xmlelement("ALL_ROWS",
	xmlagg(
		xmlelement("RESULT",
			xmlforest(sctr_id as "SCTR_ID", azon_cd as "Azon_CD",last_updt_user_id as "LAST_UPDT_USER_ID")
		)
	)
)
from sctr_simu;  

Select allOpen in new window

when i tried this query it is taking long time to execute, I need this clob to be written in a file, can you please tell me how to do that?

>>when i tried this query it is taking long time to execute

It is building XML for 20,000 rows.

>>I need this clob to be written in a file, can you please tell me how to do that?

Check out below (a stand-alone test case):

--drop table tab1 purge;
--create table tab1( sctr_id number, azon_cd number, last_updt_user_id varchar2(10));
--
--insert into tab1 values(7901,859,'MARIO');
--insert into tab1 values(1111,222,'FRED');
--commit;
--
--create or replace directory mydir as 'C:\';

create or replace procedure myproc
is
	myClob clob;
begin
	select xmlelement("ALL_ROWS",
		xmlagg(
			xmlelement("RESULT",
				xmlforest(sctr_id as "SCTR_ID", azon_cd as "Azon_CD",last_updt_user_id as "LAST_UPDT_USER_ID")
			)
		)
	).getclobval() into myClob
	from tab1;

	DBMS_XSLPROCESSOR.clob2File(myClob,'MYDIR', 'myxml.xml');
end;
/

show errors

exec myproc

Select allOpen in new window

This is the error I get on using the above procedure..

ORA-06550: line 19, column 4:
PLS-00201: identifier 'DBMS_XSLPROCESSOR.CLOB2FILE' must be declared
ORA-06550: line 19, column 4:
PL/SQL: Statement ignored

-Regards

@slightwv

Please help me resolve this.

Thanks

I am yet to recieve any information on this. should I close this and open a new question?

Sorry but I don't work on this site on weekends.

You might just need to be granted permisson.

What is your version of Oracle (all 4 numbers)?

Can you connect as SYS or system and describe DBMS_XSLPROCESSOR?  It might not be installed.

You could could use UTL_FILE code you have to write out the CLOB in chunks.  The DBMS_XSLPROCESSOR was just a single/easy way to do it all.

Sorry I am not aware of your working days.

Can you please help me in the UTIL_FILE code to write the clob.

Thank you.


-Regards

Should be the same as the code in your original post.

I'm away from the office today but you can find examples on writing a clob to file if you search.

Okay thank you.

I would spend a little time to see if dbms_xslprocessor to work.  It is much simpler.

Thank you