This discussion is archived
4 Replies Latest reply: Mar 19, 2013 7:37 AM by JackK RSS

Creating XML from query result (how?)

JackK Explorer
Currently Being Moderated
Hi,
It's a very general question. That is the simplest way to create XML containing data returned by a query?
I've got 3 tables: 2 primary and a linked table. One of the primary tables contains a CLOB column. I need to export the tables to a XML file. That is the simplest way to do that? (There is many XML-related functions in Oracle XML DB and I don't know which may be good.)

Thanks in advance,
JackK
  • 1. Re: Creating XML from query result (how?)
    odie_63 Guru
    Currently Being Moderated
    See "Generating XML Data from the Database" :
    http://docs.oracle.com/cd/B19306_01/appdev.102/b14259/xdb13gen.htm#sthref1482

    My general preference goes to SQL/XML functions as it provides the best flexibility in the format.
  • 2. Re: Creating XML from query result (how?)
    MarcoGralike Oracle ACE Director
    Currently Being Moderated
    That is the simplest way to create XML containing data returned by a query?
    Simplest:
    SQL> set long 10000
    SQL> set pages 5000
    SQL> show user
    USER is "SYS"
    SQL>  select XMLTYPE(CURSOR(select * from user_objects where rownum = 1)) as SIMPLEST
      2  from dual;
    
    SIMPLEST
    --------------------------------------------------------------------------------
    <?xml version="1.0"?>
    <ROWSET>
      <ROW>
        <OBJECT_NAME>ICOL$</OBJECT_NAME>
        <OBJECT_ID>20</OBJECT_ID>
        <DATA_OBJECT_ID>2</DATA_OBJECT_ID>
        <OBJECT_TYPE>TABLE</OBJECT_TYPE>
        <CREATED>16-JAN-13</CREATED>
        <LAST_DDL_TIME>16-JAN-13</LAST_DDL_TIME>
        <TIMESTAMP>2013-01-16:10:03:32</TIMESTAMP>
        <STATUS>VALID</STATUS>
        <TEMPORARY>N</TEMPORARY>
        <GENERATED>N</GENERATED>
        <SECONDARY>N</SECONDARY>
        <NAMESPACE>1</NAMESPACE>
      </ROW>
    </ROWSET>
    Edited by: Marco Gralike on Mar 13, 2013 1:59 PM
  • 3. Re: Creating XML from query result (how?)
    MarcoGralike Oracle ACE Director
    Currently Being Moderated
    Odie's solution is IMHO the best way of doing it. An example of this approach can be seen here: Re: How to generate xml file from database table

    By "linked" table, you mean database link...? Is this also a table with the mentioned CLOB column...? If so...also have a look at http://www.liberidu.com/blog/2012/07/02/xmltype-datatype-and-ora-22992/ and the referenced post http://technology.amis.nl/2012/07/02/select-a-blob-across-a-database-link-without-getting-ora-22992/
  • 4. Re: Creating XML from query result (how?)
    JackK Explorer
    Currently Being Moderated
    Thanks to all :)

Legend

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