4 Replies Latest reply: Mar 19, 2013 9:37 AM by JackK RSS

    Creating XML from query result (how?)

    JackK
      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
          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?)
            Marco Gralike
            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?)
              Marco Gralike
              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
                Thanks to all :)