Generating XML file by reading an orace table

You might be able to do everything in a single query without PL/SQL.

Please provide a simple test case with the table, sample data and expected XML that is close to what you need.

While we wait you can look up the XMLELEMENT, XMLAGG and possible XMLFOREST SQL functions.

Here is XMLELEMENT:
https://docs.oracle.com/en/database/oracle/oracle-database/12.2/sqlrf/XMLELEMENT.html#GUID-DEA75423-00EA-4034-A246-4A774ADC988E

The rest are in the same doc and all come with examples.

Thanks for your response slightwv.

The table is called ar_data and has the following columns
BATCH_ID
BATCH_DT
TRANS_ID
EFFDT
DESCR
TRAN_CODE
BUSINESS_UNIT_GL
FUND_CODE
DEPTID
ACCOUNT
BUSINESS_UNIT_PC

Every row in this AR_DATA table corresponds to one <AR_DTL> record.

The <AR_HDR> attributes come from say AR_HDR_DATA Table whch has the following columns
BATCH_ID
BATCH_DT
OPRID

But there will only be one record in this table.

Sample data and expected result?

Sure, I could try and guess but I would rather provide a tested example based on your actual provided information.

If the batch_id and batch_dt is the same for ALL the DTL records, I don't see the need to keep repeating in the XML.

I assume batch_id and batch_dt are the columns used to join the two tables?

The sample XML data output is in the original question. The batch id and date should repeat in every detail record. That is the format of the output file. There is no join between the 2 tables. The header table will just have one record and basically query that record for the header record in the XML output file. The XML output file will then be SFTPed to the client.
Below is how the output file should look.

<?xml version="1.0" encoding="UTF-8"?>
<AR_HDR>
<BATCH_ID>11</BATCH_ID>
<BATCH_DT>20180722</BATCH_DT>
<OPRID>INTEG</OPRID>
<AR_DTL>
<BATCH_ID>11</BATCH_ID>
<BATCH_DT>20180722</BATCH_DT>
<TRANS_ID>A820311</TRANS_ID>
<EFFDT>20180721</EFFDT>
<DESCR>Fee</DESCR>
<TRAN_CODE>C</TRAN_CODE>
<BUSINESS_UNIT_GL>xxxx</BUSINESS_UNIT_GL>
<FUND_CODE>90</FUND_CODE>
<DEPTID>1234</DEPTID>
<ACCOUNT>56789</ACCOUNT>
<BUSINESS_UNIT_PC/>
</AR_DTL>
<AR_DTL>
<BATCH_ID>11</BATCH_ID>
<BATCH_DT>20180722</BATCH_DT>
<TRANS_ID>A820311</TRANS_ID>
<EFFDT>20180723</EFFDT>
<DESCR>Payment</DESCR>
<TRAN_CODE>D</TRAN_CODE>
<BUSINESS_UNIT_GL>yyyy</BUSINESS_UNIT_GL>
<FUND_CODE>20</FUND_CODE>
<DEPTID>1234</DEPTID>
<ACCOUNT>56789</ACCOUNT>
<BUSINESS_UNIT_PC/>
</AR_DTL>
</AR_HDR>

>> There is no join between the 2 tables. The header table will just have one record and basically query that record for the header record in the XML output file.

Then do you even need the HDR table?  Can you not hard-code the values at runtime?

>>The sample XML data output is in the original question

Cool, now provide the table data that would generate that output.

I'm looking for a complete test case when I can create the ar_data table, insert some rows into it and generate the output you want.

See if this helps, I created a table similar to yours, inserted some data and think the XML is close.

I hard-coded the values you have in the HDR table.  If you want me to create that as well, let me know.  I'm just not seeing the need to insert a single row to generate XML versus just hard-coding the values.

It doesn't have ALL the data/columns but I figured you could add those?

drop table tab1 purge;
create table tab1(batch_id number, batch_dt varchar2(8), trans_id varchar2(7), fund_code number);
insert into tab1 values(11,'20180722','A820311',90);
insert into tab1 values(11,'20180722','A820311',20);
commit;

select xmlserialize(document
	xmlelement("AR_HDR",
		xmlforest(
			11 as batch_id,
			20180722 as batch_date,
			'INTEG' as oprid
		),
		xmlagg(
			xmlelement("AR_DTL",
				xmlforest (batch_id, batch_dt,	trans_id, fund_code)
			)
		) 
	)
version '1.0' indent)
from tab1
/
	

Select allOpen in new window

My results:

<?xml version="1.0"?>
<AR_HDR>
  <BATCH_ID>11</BATCH_ID>
  <BATCH_DATE>20180722</BATCH_DATE>
  <OPRID>INTEG</OPRID>
  <AR_DTL>
    <BATCH_ID>11</BATCH_ID>
    <BATCH_DT>20180722</BATCH_DT>
    <TRANS_ID>A820311</TRANS_ID>
    <FUND_CODE>90</FUND_CODE>
  </AR_DTL>
  <AR_DTL>
    <BATCH_ID>11</BATCH_ID>
    <BATCH_DT>20180722</BATCH_DT>
    <TRANS_ID>A820311</TRANS_ID>
    <FUND_CODE>20</FUND_CODE>
  </AR_DTL>
</AR_HDR>

Select allOpen in new window

Thanks slightwv. This helps a lot. Can I bother you with a few more questions -

1. If the data is NULL, then the XML output shows a space in the output.
See below -
<PROJECT_ID> </PROJECT_ID>
<ACTIVITY_ID> </ACTIVITY_ID>
<BUDGET_REF> </BUDGET_REF>

These values are actually null in the underlying table. How can this be fixed?

2. The output is clob type. How can I write it to a file.

3. Is there any limitation on the size of the clob. Sometimes, we can have 80,000 records that need to be processed and the XML file can be as big as 30 MB. Can I still do this in PL/SQL package?

I have this stored procedure.
PROCEDURE populate_ar_xml_file IS
  ctx dbms_xmlgen.ctxhandle;
  resultset sys_refcursor;
  resultset_xml xmltype;
BEGIN
  dbms_output.enable(NULL);
  open resultset for
    select *
    from tab1;

  ctx := dbms_xmlgen.newcontext(resultset);
  dbms_xmlgen.setrowsettag(ctx, 'AR_HDR');
  dbms_xmlgen.setrowtag(ctx, 'AR_DTL');

  resultset_xml := dbms_xmlgen.getxmltype(ctx);
  dbms_xmlgen.closecontext(ctx);
  close resultset;

  dbms_output.put_line(resultset_xml.getStringVal());
END populate_ar_xml_file;

However, I am getting a
ORA-19011: character string buffer too small
ORA-06152: at SYS.XMLTYPE line 169

This is at dbms_output.put_line

How can I directly write the contents of resultset_xml.getStringVal() to a flat file.

Thanks

Now you will start to see how you providing me with the test data would solve these problems.   You don't have the data types of the columns in the table so I had to make them up.

If I had your exact sample, I would provide tested code and the question would be solved.  We wouldn't need to keep going back and forth.

What is the data type of those three fields?

1:  I cannot reproduce what you are seeing.

Using the test case above:
insert into tab1 values(11,null,null,null);

I now get:

<?xml version="1.0"?>
<AR_HDR>
  <BATCH_ID>11</BATCH_ID>
  <BATCH_DATE>20180722</BATCH_DATE>
  <OPRID>INTEG</OPRID>
  <AR_DTL>
    <BATCH_ID>11</BATCH_ID>
    <BATCH_DT>20180722</BATCH_DT>
    <TRANS_ID>A820311</TRANS_ID>
    <FUND_CODE>90</FUND_CODE>
  </AR_DTL>
  <AR_DTL>
    <BATCH_ID>11</BATCH_ID>
    <BATCH_DT>20180722</BATCH_DT>
    <TRANS_ID>A820311</TRANS_ID>
    <FUND_CODE>20</FUND_CODE>
  </AR_DTL>
  <AR_DTL>
    <BATCH_ID>11</BATCH_ID>
  </AR_DTL>
</AR_HDR>

Select allOpen in new window

2: I would use SQLPLUS and spool it.  In your procedure you are using DBMS_OUTPUT.PUT_LINE.  That only outputs to the screen so I assume SPOOL is acceptable as well.

Create a file names q.sql.

Paste this in it:

set pages 0
set lines 10000
set long 1000000

spool myxml.xml
select 'hello world' from dual;
spool off

Select allOpen in new window

Log into sqlplus and do:
@q.sql

You should have your file.  Replace my select from DUAL with the XML generating select you end up with.

DO NOT copy/paste and think it is the same thing.  It won't be.


3:  A single CLOB will hold 4Gig of data.  That isn't the problem.  It is DBMS_OUTPUT that deals with STRINGS.

If you feel that you MUST use PL/SQL when I've already shown it isn't necessary then feel free.  If you want to use DBMS_OUTPUT, you need to use SUBSTR and display it in chunks.  You still have the problem of capturing the screen output.

If you still want to do it, you will probably want to use UTL_FILE to write a file on the database server.

Presonally, I would use the REALLY REALLY SIMPLE sqlplus and straight SQL.