CODEWITHSUNDEEP
xmloracle-database
shyam
shyam

Reputation: 1

How to generate XML OutPut in stored Procedures

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!!

Upvotes: 0

Views: 5672

Answers (2)

cagcowboy
cagcowboy

Reputation: 30838

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.

Upvotes: 2

Jon Heller
Jon Heller

Reputation: 36807

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.

Upvotes: 3

Related Questions