1. Get rid of all advertisements and get unlimited access to documents by upgrading to Premium Membership. Upgrade to Premium Now and also get a Premium Badge!

Generate XML from SQL select query

Discussion in 'SQL PL/SQL' started by juan, Dec 8, 2008.

  1. juan

    juan Active Member

    Messages:
    18
    Likes Received:
    2
    Trophy Points:
    90
    hi all, is it possible to generate XML from a SQL query in Oracle? If yes, I actually want to generate XML from a select query. For example a very simple select query to generate XML can be:
    Code (Text):

    Select * from home, office
    where home.id = office.id
     
    Thanks in advance :)
     
  2. tyro

    tyro Forum Genius

    Messages:
    368
    Likes Received:
    20
    Trophy Points:
    260
    Location:
    India
    hi, you need to use the dbms_xmlgen package to generate XML from sql. You will need to set a few settings first. I tried and this worked for me
    Code (Text):

    SQL> set pages 0
    SQL> set linesize 150
    SQL> set long 9999999
    SQL> set head off
    SQL> select dbms_xmlgen.getxml('Select * from home, office
    where home.id = office.id') from dual;
     
    Check this out and let me know the results.
     
  3. xmlorb

    xmlorb Guest

    dbms_xmlgen is very old school now. To create real working XML you need modern tools - such as the new open source Open-XDX tool and the CAM editor visual database browser and XML designer.

    This allows you to visually compose the SQL mapping and XML you need via drag and drop assignments - then execute the Open-XDX engine - and it reads your data and populates the XML output for you.

    You can see this all in this youtube video demonstration video (see CAMeditor collection) and then download the open source tools yourself from the CAMeditor dot org project site.

    Enjoy.
     
  4. Bharat

    Bharat Community Moderator Forum Guru

    Messages:
    1,747
    Likes Received:
    147
    Trophy Points:
    1,805
    Location:
    Vijayawada, India
    Hi tyro,

    Really some times the query frees the burden for generating xml outputs. Thanks for that query.