1 Reply Latest reply: Nov 21, 2012 9:49 PM by Mindmap RSS

    Nested XML Feed

    Mindmap
      Hello,
      I use Oracle 11g R2 SOE....

      I have two main tables

      COMMERCIALS_PROPERTIES (com_id number PK , com_size number, project_id number, com_type number)
      COM_PHOTOS (ID number PK , com_id number FK, content blob, mimetype varchar2)

      Please, note the following has nothing to do with my problem:
      CONTENT and MIMETYPE columns. Also, the lookup tables: PROJECTS , COM_TYPE

      In APEX ( Application Express ) we can expose a report as RESTful web service in XML format:

      I am using this query to generate the XML 1 feed, but I need to tweak the query to generate XML 2 feed.

      Is it possible, how to do it ???
       Select
            
      "COM"."COM_ID" as "COM_ID",
      "COM"."COM_SIZE" as "SIZE",
      "PROJECTS"."PROJECT_NAME_EN" as "PROJECT",
      "COM_TYPES"."COM_TYPE" as "COM_TYPE",
      
      'http://fam-erp.com/apex/erp/fateh/'||IMG.ID as "ImgURL"
      
       FROM 
      COM_PHOTOS IMG inner join COMMERCIALS_PROPERTIES "COM"
      on   IMG.COM_ID = COM.COM_ID
      inner join "PROJECTS" "PROJECTS" 
      on "PROJECTS"."PROJECT_ID"="COM"."PROJECT_ID"
      inner join "COM_TYPE_LOOKUP" "COM_TYPES" 
      on "COM_TYPES"."TYPE_ID"="COM"."COM_TYPE"
           
       WHERE
        COM.COM_ID < 80 order by 1
      h1. XML 1
      h2. Please look only at <COM_ID> and <ImgURL>
      <ROWSET>
      <ROW>
      <COM_ID>77</COM_ID>
      <SIZE>842</SIZE>
      <PROJECT>Bayswater Tower</PROJECT>
      <COM_TYPE>Office</COM_TYPE>
      <ImgURL>http://fam-erp.com/apex/erp/fateh/1410</ImgURL>
      </ROW>
      
      <ROW>
      <COM_ID>77</COM_ID>
      <SIZE>842</SIZE>
      <PROJECT>Bayswater Tower</PROJECT>
      <COM_TYPE>Office</COM_TYPE>
      <ImgURL>http://fam-erp.com/apex/erp/fateh/1412</ImgURL>
      </ROW>
      
      <ROW>
      <COM_ID>78</COM_ID>
      <SIZE>756</SIZE>
      <PROJECT>Bayswater Tower</PROJECT>
      <COM_TYPE>Office</COM_TYPE>
      <ImgURL>http://fam-erp.com/apex/erp/fateh/1425</ImgURL>
      </ROW>
      
      <ROW>
      <COM_ID>78</COM_ID>
      <SIZE>756</SIZE>
      <PROJECT>Bayswater Tower</PROJECT>
      <COM_TYPE>Office</COM_TYPE>
      <ImgURL>http://fam-erp.com/apex/erp/fateh/1429</ImgURL>
      </ROW>
      
      </ROWSET>
      ---------------------------
      h1. XML 2
      h2. Please look only at <COM_ID> and <Images> and <ImgURL>
      <ROWSET>
      <ROW>
      <COM_ID>77</COM_ID>
      <SIZE>842</SIZE>
      <PROJECT>Bayswater Tower</PROJECT>
      <COM_TYPE>Office</COM_TYPE>
      <Images>
            <ImgURL>http://fam-erp.com/apex/erp/fateh/1410</ImgURL>
            <ImgURL>http://fam-erp.com/apex/erp/fateh/1412</ImgURL>
      </Images>
      </ROW>
      
      <ROW>
      <COM_ID>78</COM_ID>
      <SIZE>756</SIZE>
      <PROJECT>Bayswater Tower</PROJECT>
      <COM_TYPE>Office</COM_TYPE>
      <Images>
              <ImgURL>http://fam-erp.com/apex/erp/fateh/1425</ImgURL>
              <ImgURL>http://fam-erp.com/apex/erp/fateh/1429</ImgURL>
      </Images>
      </ROW>
      </ROWSET>