Avatar of Aravindan GP
Aravindan GP
Flag for United States of America asked on

Generating XML file using Oracle Pl/sql

Hi,

Please provide me a sample procedure which does this..

I need three columsn to be selected from table
which should be formed like this.

<RESULT>
  <SCTR_ID>7901</SCTR_ID>
  <Azon_CD>859</AzON_CD>
  <LAST_UPDT_USER_ID>MARIO</LAST_UPDT_USER_ID>
</RESULT>

And this table has 20k records, how to achieve this?

Regards
Oracle DatabaseDatabases

Avatar of undefined
Last Comment
Aravindan GP

8/22/2022 - Mon
Anuradha Goli

slightwv (䄆 Netminder)

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;

Open in new window

Aravindan GP

ASKER
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..
This is the best money I have ever spent. I cannot not tell you how many times these folks have saved my bacon. I learn so much from the contributors.
rwheeler23
slightwv (䄆 Netminder)

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.
Aravindan GP

ASKER
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?
slightwv (䄆 Netminder)

>>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;  

Open in new window

Get an unlimited membership to EE for less than $4 a week.
Unlimited question asking, solutions, articles and more.
Aravindan GP

ASKER
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?
slightwv (䄆 Netminder)

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

Open in new window

Aravindan GP

ASKER
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
I started with Experts Exchange in 2004 and it's been a mainstay of my professional computing life since. It helped me launch a career as a programmer / Oracle data analyst
William Peck
Aravindan GP

ASKER
@slightwv

Please help me resolve this.

Thanks
Aravindan GP

ASKER
I am yet to recieve any information on this. should I close this and open a new question?
slightwv (䄆 Netminder)

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.
Get an unlimited membership to EE for less than $4 a week.
Unlimited question asking, solutions, articles and more.
Aravindan GP

ASKER
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
slightwv (䄆 Netminder)

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.
Aravindan GP

ASKER
Okay thank you.
Experts Exchange is like having an extremely knowledgeable team sitting and waiting for your call. Couldn't do my job half as well as I do without it!
James Murphy
slightwv (䄆 Netminder)

I would spend a little time to see if dbms_xslprocessor to work.  It is much simpler.
ASKER CERTIFIED SOLUTION
slightwv (䄆 Netminder)

Log in or sign up to see answer
Become an EE member today7-DAY FREE TRIAL
Members can start a 7-Day Free trial then enjoy unlimited access to the platform
Sign up - Free for 7 days
or
Learn why we charge membership fees
We get it - no one likes a content blocker. Take one extra minute and find out why we block content.
Not exactly the question you had in mind?
Sign up for an EE membership and get your own personalized solution. With an EE membership, you can ask unlimited troubleshooting, research, or opinion questions.
ask a question
Aravindan GP

ASKER
Thank you