How to fetch data from oracle in xml format?

[premkumar sp]

These are my emp table records
EmpId EmpName
1 AAA
2 CCC
3 BBB

I want to generate xml in below format.
<emp>
<1>AAA</1>
<2>CCC</2>
<3>BBB</3>
</emp>

What is the required sql query?

[amitpatel66]

Are you looking at something like this:

with t as (select 1 empid, 'AAA' name from dual
union all select 2, 'BBB' from dual
union all select 3,'CCC' from dual)
SELECT XMLELEMENT("EMP",XMLAGG(XMLELEMENT(empid,XMLATTRIBUTES(empid),name))) from t
/
 
<EMP><EMPID EMPID="1">AAA</EMPID><EMPID EMPID="2">BBB</EMPID><EMPID EMPID="3">CCC</EMPID></EMP>
 
 

[amitpatel66]

The way you want the XML to be generated will fail in parsing because XML tag changes as per empid.

You can:

with t as (select 1 empid,'AAA' name FROM dual
union all select 2, 'BBB' from dual
union all select 3,'CCC' from dual)
select col from
(SELECT '<EMP>' col,0 FROM dual
UNION
SELECT '<'||empid||'>'||name||'</'||empid||'>' col,rownum rn from t
UNION
SELECT '</EMP>',NULL FROM dual
ORDER BY 2 NULLS LAST)
/
 
<EMP>
<1>AAA</1>
<2>BBB</2>
<3>CCC</3>
</EMP>
 
 

[premkumar sp]

Is the sql query not possible by using xml functions like xmlelement,xmlquery?

[amitpatel66]

You can try something like this:

with t as (select 1 empid,'AAA' ename from dual
union all select 2,'BBB' from dual)
SELECT  REPLACE(col1,'ROWSET','EMP') FROM
(SELECT SYS_XMLAGG (XMLELEMENT(ename,XMLATTRIBUTES(empid),ename)) col1 from t)
/
 
col1
-----
 
"<?xml version="1.0"?>
<EMP>
<ENAME EMPID="1">AAA</ENAME><ENAME EMPID="2">BBB</ENAME></EMP>
"