4 Replies Latest reply: Nov 22, 2012 3:03 PM by Solomon Yakobson RSS

    Retrieve Data from oracle database in xml format

    975701
      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
          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
            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
              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
                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.