| PL/SQL Cursor output as XML [message #294407] |
Thu, 17 January 2008 08:31  |
rkaula
Messages: 22
Registered: December 2005
|
Junior Member |
|
|
Is it possible to transform the output of PL/SQL cursor with dbms_output statements as XML. Any utility??? For example,
create table xyz
(a1 integer,
a2 varchar2(5));
insert into xyz values(1, 'xxx');
insert into xyz values(2, 'yxx');
insert into xyz values(3, 'xxx');
insert into xyz values(4, 'xxk');
declare
begin
for c_row in (select * from xyz)
loop
if c_row.a2 = 'xxx' then
dbms_output.put_line(c_row.a1 || c_row.a2 || 'Correct');
else
dbms_output.put_line(c_row.a1 || c_row.a2 || 'Not Correct');
end if;
end loop;
end;
Suppose I want the output of dbms_output statement appear as XML?
Thanks.
Rajeev.
|
|
|
|
| Re: PL/SQL Cursor output as XML [message #294428 is a reply to message #294407] |
Thu, 17 January 2008 10:08  |
 |
Michel Cadot
Messages: 68716
Registered: March 2007
Location: Saint-Maur, France, https...
|
Senior Member
Account Moderator |
|
|
SQL> select dbms_xmlquery.getxml('select * from emp where rownum <= 3') from dual;
DBMS_XMLQUERY.GETXML('SELECT*FROMEMPWHEREROWNUM<=3')
--------------------------------------------------------------------------------------
<?xml version = '1.0'?>
<ROWSET>
<ROW num="1">
<EMPNO>7369</EMPNO>
<ENAME>SMITH</ENAME>
<JOB>CLERK</JOB>
<MGR>7902</MGR>
<HIREDATE>12/17/1980 0:0:0</HIREDATE>
<SAL>800</SAL>
<DEPTNO>20</DEPTNO>
</ROW>
<ROW num="2">
<EMPNO>7499</EMPNO>
<ENAME>ALLEN</ENAME>
<JOB>SALESMAN</JOB>
<MGR>7698</MGR>
<HIREDATE>2/20/1981 0:0:0</HIREDATE>
<SAL>1600</SAL>
<COMM>300</COMM>
<DEPTNO>30</DEPTNO>
</ROW>
<ROW num="3">
<EMPNO>7521</EMPNO>
<ENAME>WARD</ENAME>
<JOB>SALESMAN</JOB>
<MGR>7698</MGR>
<HIREDATE>2/22/1981 0:0:0</HIREDATE>
<SAL>1250</SAL>
<COMM>500</COMM>
<DEPTNO>30</DEPTNO>
</ROW>
</ROWSET>
Forgot: please read and follow OraFAQ Forum Guide, especially "How to format your post?" section.
Make sure that lines of code do not exceed 80 characters when you format. Use the "Preview Message" button.
Regards
Michel
[Updated on: Thu, 17 January 2008 10:09] Report message to a moderator |
|
|
|
| Re: PL/SQL Cursor output as XML [message #294433 is a reply to message #294428] |
Thu, 17 January 2008 11:02 |
rkaula
Messages: 22
Registered: December 2005
|
Junior Member |
|
|
The output of DBMS_XMLQUERY is coming from a SQL query. My question was on generating XML based on processing result. Is there a utility that combines the processing result in one XML output. As in the script, the 4 output rows with static text displayed as XML.
Thanks.
|
|
|
|
|
|
| Re: PL/SQL Cursor output as XML [message #294447 is a reply to message #294438] |
Thu, 17 January 2008 12:22 |
rkaula
Messages: 22
Registered: December 2005
|
Junior Member |
|
|
|
Generally it seems XML output from tables involves SQL query. However, is it possible to combine PL/SQL processing results in one XML output. Maybe separate IF statements in the logic deliver different outputs, which need to be combined in one XML output.
|
|
|
|
|
|
|
|
| Re: PL/SQL Cursor output as XML [message #294500 is a reply to message #294461] |
Thu, 17 January 2008 16:27 |
rkaula
Messages: 22
Registered: December 2005
|
Junior Member |
|
|
dbms_xmlquery is great. It gets the table data out as XML. But, how will dbms_xmlquery work if some of the output values also include variable values or static text within a PL/SQL program unit? Maybe I am not able to explain what the question clearly. Let me try again.
declare
t1 varchar2(5) := 'abc';
begin
for c_row in (select * from xyz)
loop
if condition1 then
dbms_output.put_line(c_row.a1 || c_row.a2 ||t1|| 'Correct');
end if;
. . .
if condition2 then
dbms_output.put_line(c_row.a1 || c_row.a2 ||t1|| 'Not Correct');
end if;
end loop;
end;
Is it possible to display the dbms_output lines above which have a mix of table data, variable value, and static text as XML. I guess one can manually add tags, but it would be better if there is a utility that enables one to collect the values and then spit out the XML.
|
|
|
|
| Re: PL/SQL Cursor output as XML [message #294540 is a reply to message #294407] |
Thu, 17 January 2008 23:42 |
hobbes
Messages: 173
Registered: January 2006
|
Senior Member |
|
|
Maybe the example you've given is a simplified one, but at least here your conditions can be included in the select itself with CASE/DECODE to print out the static text/variable values within XML.
SQL> select dbms_xmlgen.getxml('select a1
2 , a2
3 , DECODE(a2,'||''''||'xxx'||''''||','||''''
4 ||'Correct'||''''||','||''''
5 ||'Not Correct'||''''||') a3
6 from xyz') xmldata
7 from dual;
XMLDATA
--------------------------------------------------------------------------------
<?xml version="1.0"?>
<ROWSET>
<ROW>
<A1>1</A1>
<A2>xxx</A2>
<A3>Correct</A3>
</ROW>
<ROW>
<A1>2</A1>
<A2>yxx</A2>
<A3>Not Correct</A3>
</ROW>
<ROW>
<A1>3</A1>
<A2>xxx</A2>
<A3>Correct</A3>
</ROW>
<ROW>
<A1>4</A1>
<A2>xxk</A2>
<A3>Not Correct</A3>
</ROW>
</ROWSET>
Then there is XMLELEMENT which adds tags to individual elements, but if you have to use dbms_output.put_line to print out each element that won't make things better - the cleaner way would be to manually add tags.
If your requirements are more complex that that you might want to look at XMLDOM package for generating XML - that gives a lot of flexibility to the way XML can be structured.
|
|
|
|
] 
Blog
Search
Help
Members
Register
Login
Home
