Hi AskTOM Team,


I have the below code, I got the desired output. But I want to convert the output into XML format.
Please send the code for covert xml format

The output of the below code is table datas, I want to convert the datas into xml format. how to achieve it.

CODE:
=====

set serveroutput on;
declare
p_query varchar2(32767);
l_theCursor integer default dbms_sql.open_cursor; --open_cursor: to process sql statment we must have open cursor
l_columnValue varchar2(4000);
l_status integer;
l_descTbl dbms_sql.desc_tab; --desc_tab : contains column information
l_colCnt number;
n number := 0;
L_TAB_NAME TABLE_TYPE;
l_table_name varchar2(100);

procedure p(msg varchar2) is
l varchar2(4000) := msg;
begin
while length(l) > 0
loop
dbms_output.put_line('Data '||substr(l,1,80));
l := substr(l,81);
end loop;
end;
begin


SELECT TABLE_NAME BULK COLLECT INTO L_TAB_NAME FROM USER_TAB_COLUMNS
WHERE COLUMN_NAME='ENQUIRY_NO';

FOR INDX IN 1..L_TAB_NAME.COUNT
LOOP

l_table_name:= L_TAB_NAME(INDX);

dbms_output.put_line('Table Name '||l_table_name);
p_query:= 'select * from '||l_table_name||' where rownum<10';


execute immediate 'alter session set nls_date_format=''dd-mon-yyyy hh24:mi:ss'' ';

dbms_sql.parse( l_theCursor, p_query, dbms_sql.native ); --parse: SQL statement must be parsed by calling the PARSE Procedures
--Native: Specifies normal behavior for the database to which the program is connected
dbms_sql.describe_columns( l_theCursor, l_colCnt, l_descTbl ); --describe_columns contains dynamic cursor information


for i in 1 .. l_colCnt
loop
dbms_sql.define_column(l_theCursor, i, l_columnValue, 4000); --define_column: to receive the select values
end loop;


l_status := dbms_sql.execute(l_theCursor); -- execute: execute the query

while ( dbms_sql.fetch_rows(l_theCursor) > 0 )
loop -- fetch_rows: successfully fetch the rows
for i in 1 .. l_colCnt
loop
dbms_sql.column_value( l_theCursor, i, l_columnValue ); --column_value: to determine the value of a column retrieved by the FETCH_ROWS call
p( rpad( l_descTbl(i).col_name, 30 )|| ': ' ||l_columnValue );
end loop;
dbms_output.put_line( '-----------------' );
n := n + 1;
end loop;
if n = 0 then
dbms_output.put_line( chr(10)||'No data found '||chr(10) );
end if;

END LOOP;

end;


The below is the output I got but I need xml output, example of the xml output is shown in last please scroll down
OUTPUT:
========

anonymous block completed
Table Name ABANDONED_ACCOUNTS
Data ENQUIRY_NO : ZWT0NZ5D
Data ACCOUNT_NO : 66032376
Data ENQUIRY_TIMESTAMP : 09-dec-2016 15:08:07
Data SCORING_STAGE : 20
Data RESPONSE_METHOD : 0
Data ORDER_DATE : 09-dec-2016 15:13:40
Data ORDER_VALUE : 0
Data ORDER_INDICATOR : N
Data CREDIT_BAND : Y4
Data CREDIT_LIMIT : 150
Data CA_ACCEPT_FLAG : t
Data BRAND : Littlewoods All Inclusive
Data ORDER_PLACED_FLAG : Y
Data ABANDONED_FLAG : N
Data FIRST_PROD_CODE : 4G9ND
Data SECOND_PROD_CODE : K7GUF
Data REPORT_FLAG : F
Data ORDER_AMOUNT : 283.2
-----------------
Data ENQUIRY_NO : ZWT0NZ5F
Data ACCOUNT_NO : 66032378
Data ENQUIRY_TIMESTAMP : 09-dec-2016 15:08:09
Data SCORING_STAGE : 20
Data RESPONSE_METHOD : 0
Data ORDER_DATE : 09-dec-2016 15:12:20
Data ORDER_VALUE : 124
Data ORDER_INDICATOR : Y
Data CREDIT_BAND : U1
Data CREDIT_LIMIT : 0
Data CA_ACCEPT_FLAG :
Data BRAND : Company 8
Data ORDER_PLACED_FLAG : Y
Data ABANDONED_FLAG : N
Data FIRST_PROD_CODE : 73JPE
Data SECOND_PROD_CODE :
Data REPORT_FLAG : Q
Data ORDER_AMOUNT : 120

table_type:

create or replace
TYPE table_type IS TABLE OF varchar2(3200);


below is the expected xml format

XML FORMAT:
===========

<?xml version="1.0"?>
<ENQUIRY_NO_E35GJPM6>
<ENQUIRY_DATA_PIECE>
<ROW>
<ENQUIRY_NO>E35GJPM6</ENQUIRY_NO>
<SCORING_STAGE>0</SCORING_STAGE>
<DATA_PIECE_ID>DP00019</DATA_PIECE_ID>
<DATA_PIECE_VALUE>SO182AE</DATA_PIECE_VALUE>
<DATA_PIECE_TIMESTAMP/>
</ROW>
<ROW>
<ENQUIRY_NO>E35GJPM6</ENQUIRY_NO>
<SCORING_STAGE>0</SCORING_STAGE>
<DATA_PIECE_ID>DP08053</DATA_PIECE_ID>
<DATA_PIECE_VALUE>f</DATA_PIECE_VALUE>
<DATA_PIECE_TIMESTAMP/>
</ROW>

create table t as
  select rownum x, sysdate y from dual connect by level <= 5;
  
select dbms_xmlgen.getxml('select * from t') from dual;

DBMS_XMLGEN.GETXML('SELECT*FROMT')
-----------------------------------
<?xml version="1.0"?>
<ROWSET>
 <ROW>
  <X>1</X>
  <Y>10-JUL-17</Y>
 </ROW>
 <ROW>
  <X>2</X>
  <Y>10-JUL-17</Y>
 </ROW>
 <ROW>
  <X>3</X>
  <Y>10-JUL-17</Y>
 </ROW>
 <ROW>
  <X>4</X>
  <Y>10-JUL-17</Y>
 </ROW>
 <ROW>
  <X>5</X>
  <Y>10-JUL-17</Y>
 </ROW>
</ROWSET>