This discussion is archived
4 Replies Latest reply: Nov 22, 2012 1:03 PM by Solomon Yakobson RSS

Retrieve Data from oracle database in xml format

975701 Newbie
Currently Being Moderated
Hi there

I am running a select statement on oracle database table. it returns me the result in a table format. However, I need the result in xml format.
eg
if general output of a select statment select First_Name, Last_Name from table1 is

First_Name                Last_Name
----------------------------------------------
John                        Lewis
David                         Jones

then I require it in xml format as below

*<Row>*
*<First_Name>John</First_Name>*
*<Last_Name>Lewis</Last_Name>*
*</Row>*
*<Row>*
*<First_Name>David</First_Name>*
*<Last_Name>Jones</Last_Name>*
*</Row>*

or

*<Row First_Name="John" Last_Name="Lewis" />*
*<Row First_Name="David" Last_Name="Jones" />*


In SQLServer we use for xml auto. How can I get the xml output in oracle? please help.

Thanks
  • 1. Re: Retrieve Data from oracle database in xml format
    LPS Journeyer
    Currently Being Moderated
    Refer the docs

    http://docs.oracle.com/cd/B19306_01/appdev.102/b14259/xdb13gen.htm
  • 2. Re: Retrieve Data from oracle database in xml format
    Johan Louwers Explorer
    Currently Being Moderated
    Hi,
    at this page you can find some examples on how to use DBMS_XMLGEN which is used to generate XML via SQL

    http://www.orafaq.com/wiki/DBMS_XMLGEN

    Regards,
    Johan Louwers
  • 3. Re: Retrieve Data from oracle database in xml format
    ranit B Expert
    Currently Being Moderated
    try this..
    with xx as (
         select 'John' fn,'Lewis' ln from dual union all
         select 'David' fn,'Jones' ln from dual 
    )
    select 
         '<Row First_Name="'||fn||'" Last_Name="'||ln||'" />' 
    from xx;
    gives
    <Row First_Name="John" Last_Name="Lewis" />
    <Row First_Name="David" Last_Name="Jones" />
  • 4. Re: Retrieve Data from oracle database in xml format
    Solomon Yakobson Guru
    Currently Being Moderated
    ranit B wrote:
    try this..
    It gives a non-wellformed XML. As it was aleady suggested by Johan Louwers:
    SQL> select  dbms_xmlgen.getxml(q'[
      2  select 'John' fn,'Lewis' ln from dual union all
      3  select 'David' fn,'Jones' ln from dual
      4  ]') from dual
      5  /
    
    DBMS_XMLGEN.GETXML(Q'[SELECT'JOHN'FN,'LEWIS'LNFROMDUALUNIONALLSELECT'DAVID'FN,'J
    --------------------------------------------------------------------------------
    <?xml version="1.0"?>
    <ROWSET>
     <ROW>
      <FN>John</FN>
      <LN>Lewis</LN>
     </ROW>
     <ROW>
      <FN>David</FN>
      <LN>Jones</LN>
     </ROW>
    </ROWSET>
    
    DBMS_XMLGEN.GETXML(Q'[SELECT'JOHN'FN,'LEWIS'LNFROMDUALUNIONALLSELECT'DAVID'FN,'J
    --------------------------------------------------------------------------------
    
    
    SQL> 
    Or:
    with xx as (
                select 'John' fn,'Lewis' ln from dual union all
                select 'David' fn,'Jones' ln from dual
               )
    select  xmlelement("ROWSET",xmlagg(xmlelement("ROW",xmlforest(fn,ln))))
      from  xx
    /
    
    XMLELEMENT("ROWSET",XMLAGG(XMLELEMENT("ROW",XMLFOREST(FN,LN))))
    -----------------------------------------------------------------------------------------------
    <ROWSET><ROW><FN>John</FN><LN>Lewis</LN></ROW><ROW><FN>David</FN><LN>Jones</LN></ROW></ROWSET>
    
    SQL> 
    SY.
    SY.

Legend

  • Correct Answers - 10 points
  • Helpful Answers - 5 points