How to generate XML OutPut in stored Procedures

Question

I will be getting few records from database using SQL query in a stored procedure , My requirement is that I need to convert these records in to XML format and send this XML as an OUT PARAM in the same stored Procedure.

Can you kindly help us

Thanks!!

Jon Heller · Accepted Answer · 2011-03-01 05:24:59Z

3

DBMS_XMLGEN.GETXML can turn a query into XML. For example:

select DBMS_XMLGEN.GETXML (q'!
    select 1 value1, 'asdf' value2 from dual union all
    select 2 value2, 'fdsa' value2 from dual    
!') from dual;

Returns a CLOB with this data:

<?xml version="1.0"?>
<ROWSET>
 <ROW>
  <VALUE1>1</VALUE1>
  <VALUE2>asdf</VALUE2>
 </ROW>
 <ROW>
  <VALUE1>2</VALUE1>
  <VALUE2>fdsa</VALUE2>
 </ROW>
</ROWSET>

In a stored procedure, select this into an OUT CLOB parameter.

answered Mar 1, 2011 at 5:24

Jon Heller

37.1k6 gold badges84 silver badges150 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

shyam Over a year ago

Hi Jonearles .. I implemented it , Its working fine :-) Thank u so much!!

cagcowboy · Accepted Answer · 2011-03-01 06:55:09Z

2

If your requirements are more complicated than being able to use just a SQL statement as suggested by jonearles, another option is the XMLDOM package, which will allow you to create XML using PL/SQL.

It's more complicated than DBMS_XMLGEN, but it's also more powerful.

answered Mar 1, 2011 at 6:55

cagcowboy

31.1k11 gold badges75 silver badges95 bronze badges

1 Comment

shyam Over a year ago

As of now above suggested one is working , If I face any diffucluty I will go thorugh your suggestion also .. Thanks :-)

Collectives™ on Stack Overflow

How to generate XML OutPut in stored Procedures

2 Answers 2

1 Comment

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

1 Comment

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related