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:

[code=oracle]
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(XMLELE MENT(empid,XMLA TTRIBUTES(empid ),name))) from t
/

<EMP><EMPID EMPID="1">AAA</EMPID><EMPID EMPID="2">BBB</EMPID><EMPID EMPID="3">CCC</EMPID></EMP>

[/code]

[amitpatel66]

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

You can:

[code=oracle]
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>

[/code]

[premkumar sp]

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

[amitpatel66]

You can try something like this:

[code=oracle]
with t as (select 1 empid,'AAA' ename from dual
union all select 2,'BBB' from dual)
SELECT REPLACE(col1,'R OWSET','EMP') FROM
(SELECT SYS_XMLAGG (XMLELEMENT(ena me,XMLATTRIBUTE S(empid),ename) ) col1 from t)
/

col1
-----

"<?xml version="1.0"?>
<EMP>
<ENAME EMPID="1">AAA</ENAME><ENAME EMPID="2">BBB</ENAME></EMP>
"

[/code]