1 2 Previous Next 15 Replies Latest reply: Dec 17, 2012 3:28 PM by fac586 RSS

    Query to generate 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

      I Exposed 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>
      
       
        • 1. Re: Query to generate Nested XML feed
          Prabodh
          Fateh,

          I really doubt if the nested XML can be generated from a Report Region. The <ROWSET> and <ROW> tags point to a generic XML generator that takes a SELECT as input. Much like SYS_XMLGEN or DBMS_XMLGEN.

          You will need to explore alternate mechanism for the nested XML.

          Below are 2 links to get you started on the SQL part of it. For deployment you can use ApexListener ResourceTemplates, if you use ApexListener.

          If you are on Apex 4.2 and ApexListener 2.0EA then you have more options as you can define the RESTful service modules in SQLWorkshop.

          http://asktom.oracle.com/pls/asktom/f?p=100:11:0::::P11_QUESTION_ID:4980337843276
          http://asktom.oracle.com/pls/asktom/f?p=100:11:::::P11_QUESTION_ID:11650482108651

          Regards,
          • 2. Re: Query to generate Nested XML feed
            Mindmap
            Thanks,

            I used LISTAGG to have the images on one row and the other party who will receive the feed will fix it ...

            Regards,
            Fateh
            • 3. Re: Query to generate Nested XML feed
              Mindmap
              Hi Prabodh,
              I hope that you are still watching the thread... The solution I proposed using LISTAGG provides this output:
              * Our focus is on <Images> tag only.
              http://fam-erp.com/apex/apex_rest.getReport?app=101&page=5000&reportid=test

              Now, I must to provide nested images tag. As per your advice I have to use RESTful service modules, but I read and tried to figure out but to not avail .... Can you please give an example based on this query ?
              select
                     XMLSERIALIZE (DOCUMENT     
                     xmlelement(
                       "DEPARTMENTS",
                       xmlagg(
                         xmlelement(
                           "DEPARTMENT",
                           xmlforest(
                             d.deptno,
                             d.dname,
                             (
                               select xmlagg(
                                        xmlelement(
                                          "EMPLOYEE",
                                           xmlforest(
                                             e.empno,
                                             xmlcdata(e.ename) ename,
                                             e.hiredate
                                           )
                                         )
                                      )
                               from   emp e
                               where  e.deptno = d.deptno 
                             ) employees
                           )
                         )
                       )
                     )
                     )               
                       x     
              from   dept d;
              I am using oracle 11g R2 , APEX 4.2 , Listener 2.0 on Glassfish

              Best Regards,
              Fateh

              Edited by: Fateh on Dec 5, 2012 8:24 PM
              • 4. Re: Query to generate Nested XML feed
                Prabodh
                Fateh,

                I have not understood what you are asking.
                The snippet you (EMP, DEPT) have posted gives nested tags. The Employees is equivalent of your Images, so to speak. This part of the code
                (
                                 select xmlagg(
                                          xmlelement(
                                            "EMPLOYEE",
                                             xmlforest(
                                               e.empno,
                                               xmlcdata(e.ename) ename,
                                               e.hiredate
                                             )
                                           )
                                        )
                                 from   emp e
                                 where  e.deptno = d.deptno 
                               ) employees
                The above uses CDATA which you do not need, but the structure is similar.

                I do not have your tables and data, nor do I know the query that you are using in the report that you have provided a link to. So a specific snippet is not feasible with current information.

                If you post the current report query I might be able to suggest some solution, but it will be difficult for me to test it out.
                Or, is it related to how to setup RESTful?

                Cheers.
                • 5. Re: Query to generate Nested XML feed
                  Mindmap
                  Thanks Prabodh,
                  is it related to how to setup RESTful?
                  Yes, I got the required report, but how can I use it within the RESTful service. In other words, how can I send it to the other party ??

                  Can you please head over to
                  http://fam-erp.com/apex/f?p=4550:1:0:::::
                  workspace: test
                  user: test
                  pwd: test

                  I just used the HR tables to make it easy for you. However, In the SQL Commands, I have saved the XML Report which generates the XML document.

                  Can you please set it up for me ?

                  Best Regards,
                  Fateh
                  • 6. Re: Query to generate Nested XML feed
                    Prabodh
                    Hi Fateh,
                    Yes, I got the required report, but how can I use it within the RESTful service. In other words, how can I send it to the other party ??
                    Can you please head over to
                    http://fam-erp.com/apex/f?p=4550:1:0:::::
                    workspace: test
                    user: test
                    pwd: test
                    >
                    I get invalid credentials.
                    >
                    I just used the HR tables to make it easy for you. However, In the SQL Commands, I have saved the XML Report which generates the XML document.

                    Can you please set it up for me ?>

                    "Serving" RESTful requires the use of ApexListener. Are you using ApexListener? If yes, which version of ApexListener.

                    To use Apex 4.2 SQLWorkshop you need ApexListener 2.0(EA) implemented. See http://docs.oracle.com/cd/E37097_01/doc/doc.42/e35128/restful_svc.htm#BABDEJCE
                    For ApexListener 1.1.x you have to define Resource Template. See RESTful APIs and Resource Templates in installation guide.

                    More information here http://docs.oracle.com/cd/E37097_01/doc/doc.42/e35129/adm_mg_service_set.htm#AEADM298
                    and http://docs.oracle.com/cd/E37097_01/doc/doc.42/e35125/advnc_web_services.htm#CHDDBGAI

                    Cheers,
                    • 7. Re: Query to generate Nested XML feed
                      Mindmap
                      Thanks,

                      I just posted a new thread {message:id=10738980}

                      Regards,
                      Fateh

                      Edited by: Fateh on Dec 12, 2012 8:35 PM
                      • 8. Re: Query to generate Nested XML feed
                        Prabodh
                        Hi Fateh,

                        I just setup the RESTful Webservice in Module named TestRest in your environment, take a look.

                        It can be access using the URL
                        http://fam-erp.com/apex/test/rest/pull/
                        Here are the details of the Web Service
                        Module: TestRest
                        URI Prefix: rest/
                        URI Template: pull/
                        Method : GET
                        Source Type: Media Resource
                        Requires Secure Access: No
                        Source :
                         select 'text/xml', <-- Important
                               XMLSERIALIZE (DOCUMENT     
                               xmlelement(
                                 "DEPARTMENTS",
                                 xmlagg(
                                   xmlelement(
                                     "DEPARTMENT",
                                     xmlforest(
                                       d.deptno,
                                       d.dname,
                                       (
                                         select xmlagg(
                                                  xmlelement(
                                                    "EMPLOYEE",
                                                     xmlforest(
                                                       e.empno,
                                                       xmlcdata(e.ename) ename,
                                                       e.hiredate
                                                     )
                                                   )
                                                )
                                         from   emp e
                                         where  e.deptno = d.deptno 
                                       ) employees
                                     )
                                   )
                                 )
                               )
                               )                    
                        from   dept d
                        Cheers,
                        • 9. Re: Query to generate Nested XML feed
                          Mindmap
                          Hello Prabodh ,

                          I highly appreciate your reply, and sorry to keep pestering you, but my query works on SQL Commands, but does not working on the RESTful. I get Internal Server Error as you can see here
                          http://fam-erp.com/apex/test/LISTING/JUST/
                          This is the code
                          select 'text/xml',
                                 XMLSERIALIZE (DOCUMENT     
                                 xmlelement(
                                   "LISTINGS",
                                   xmlagg(
                                     xmlelement(
                                       "LISTING",
                                       xmlforest(
                          'RS-'||R."RES_ID" as "Property_Ref_No",
                          'Sale' as "Ad_Type",
                          'Dubai' as "State",
                          ' ' as "Frequency",
                          R."RES_SIZE" as "Unit_Builtup_Area",
                          R.BEDS as "Bedrooms",
                          R.bath as "No_of_Bathrooms",
                          R.VIDEO as "Web_Tour",
                          'FAM Properties' as "Company_Name",
                          '04473572' as "Company_Number", (
                          select xmlagg(
                                                    xmlelement(
                                                      "IMAGES",
                                                       xmlforest(
                                                      'http://thevillaproject.com/apex/erp/IMAGES/'||IMG.ID AS "TEST"
                                                       )
                                                     )
                                                  )
                          FROM       
                                FATEH.IMAGES IMG      
                          WHERE IMG.RES_ID = R.RES_ID ) IMAGE_URL )))))
                          FROM 
                                FATEH.RES R where R.res_id < 50
                          Thanks in advance,
                          Fateh
                          • 10. Re: Query to generate Nested XML feed
                            Prabodh
                            Fateh,
                            The Unauthenticated RESTful service will be accessed by APEX_REST_PUBLIC_USER. My guess it is telling your "Table or View not found".
                            1. Grant SELECT on the tables/views to APEX_REST_PUBLIC_USER
                            2. Use fully qualified names (SCHEMA.TABLE) of Tables/Views in queries.


                            Cheers,
                            • 11. Re: Query to generate Nested XML feed
                              Mindmap
                              Hello Prabodh ,
                              The Unauthenticated RESTful service will be accessed by APEX_REST_PUBLIC_USER. My guess it is telling your "Table or View not found".
                              1. Grant SELECT on the tables/views to APEX_REST_PUBLIC_USER
                              2. Use fully qualified names (SCHEMA.TABLE) of Tables/Views in queries.
                              I followed your instruction, but it did not work.

                              The good news is I was able to pinpoint the root cause, but the bad news is that it is getting more complicated:

                              When I did the following it works fine:
                              1- I deleted these columns :
                              'FAM Properties' as "Company_Name",
                              '04473572' as "Company_Number",
                              2- Remove the concatenation string

                              However, After deleting the aforementioned, If I remove the WHERE clause it does not work.

                              In other words, This query works fine:
                              select 'text/xml',
                                     XMLSERIALIZE (DOCUMENT     
                                     xmlelement(
                                       "LISTINGS",
                                       xmlagg(
                                         xmlelement(
                                           "LISTING",
                                           xmlforest(
                              R."RES_ID" as "Property_Ref_No",
                              'Sale' as "Ad_Type",
                              'Dubai' as "State",
                              ' ' as "Frequency",
                              R."RES_SIZE" as "Unit_Builtup_Area",
                              R.BEDS as "Bedrooms",
                              R.bath as "No_of_Bathrooms",
                              R.VIDEO as "Web_Tour",
                               (
                              select xmlagg(
                                                        xmlelement(
                                                          "IMAGES",
                                                           xmlforest(
                                                          IMG.ID AS "TEST"
                                                           )
                                                         )
                                                      )
                              FROM       
                                    FATEH.IMAGES IMG      
                              WHERE IMG.RES_ID = R.RES_ID ) IMAGE_URL )))))
                              FROM 
                                    FATEH.RES R where R.res_id < 50
                              So, any ideas ?? Is it a bug ?

                              Best Regards,
                              Fateh
                              • 12. Re: Query to generate Nested XML feed
                                Prabodh
                                Not a bug.
                                The Media Resource query must return only one row !
                                My guess is that your query returns more than one rows, that is multiple XML lines.

                                If you run the query in SQLWorkshop / SQL Developer you should get only ONE LINE output for Media Resource to work.

                                SQLX functions are complex and not easy to work with ;)

                                Cheers.
                                • 13. Re: Query to generate Nested XML feed
                                  Mindmap
                                  Hello,
                                  I see, I found another solution, but come across another problem that is the web service should get Data from more than one table. I have this procedure, and I would like to *"UNION" its* Select Statement with another Select Statement:
                                  declare
                                    v_xml clob;
                                    v_retval clob := '<?xml version="1.0" encoding="iso-8859-1"?>';
                                    v_blob blob;
                                  begin
                                  select 
                                         xmlelement(
                                           "LISTINGS",
                                           xmlagg(
                                             xmlelement(
                                               "LISTING",
                                               xmlforest(
                                  'RS-'||R."RES_ID" as "Property_Ref_No",
                                  "UNIT_TYPE_LOOKUP"."UNIT_TYPE" as "Unit_Type",
                                  'Sale' as "Ad_Type",
                                  'Dubai' as "State",
                                  L.LOCATION_NAME_EN AS "Community",
                                  "PROJECTS"."PROJECT_NAME_EN" as "Property_Name",
                                  "RES_SALE"."ASKING_PRICE" as "Price",
                                  ' ' as "Frequency",
                                  ST."SUB_TYPE" as "Unit_Model",
                                  R."RES_SIZE" as "Unit_Builtup_Area",
                                  R.BEDS as "Bedrooms",
                                  R.bath as "No_of_Bathrooms",
                                  "RES_SALE"."AD_TITLE" as "Property_Title",
                                  RES_SALE.DESCRIPTION as "Web_Remarks",
                                  to_char("RES_SALE"."UPDATED_ON",'YYYY-MM-DD HH:MI:SSPM') as "Last_Updated",
                                  R.VIDEO as "Web_Tour",
                                   (
                                  select xmlagg(
                                                            
                                                               xmlforest(
                                                              'http://thevillaproject.com/apex/erp/IMAGES/'||IMG.ID AS "ImageUrl"
                                                               
                                                             )
                                                          )
                                  FROM       
                                        IMAGES IMG      
                                  WHERE IMG.RES_ID = R.RES_ID ) Images )))).getclobval()
                                  
                                    into      v_xml
                                    FROM 
                                        "RES_SALE" "RES_SALE"
                                  inner join
                                        RES R on (R."RES_ID"="RES_SALE"."RES_ID")
                                  inner join
                                            "UNIT_STATUS_LOOKUP" on (UNIT_STATUS_LOOKUP.status_id = "RES_SALE"."RES_STATUS")
                                  inner join
                                        "PROJECTS" "PROJECTS" on ("PROJECTS"."PROJECT_ID"=R."PROJECT_ID")
                                  inner join
                                        "UNIT_TYPE_LOOKUP" "UNIT_TYPE_LOOKUP" on ("UNIT_TYPE_LOOKUP"."TYPE_ID"=R."RES_TYPE")
                                  inner join
                                        "RES_SUB_TYPE_LOOKUP" ST on (ST."SUB_TYPE_ID"=R."SUB_TYPE")
                                  INNER JOIN 
                                        COMPLEX_LOOKUP CL ON (CL.COMPLEX_ID = "PROJECTS".COMPLEX_ID)
                                  INNER JOIN 
                                        LOCATIONS L ON (L.LOCATION_ID = CL.LOCATION_ID)
                                  
                                  WHERE  "RES_SALE".RES_status IN (5,8) AND "RES_SALE".LIVE = 'Y' AND "RES_SALE".DESCRIPTION IS NOT NULL
                                  ;
                                    --
                                    dbms_lob.append(v_retval,v_xml);
                                    --
                                    OWA_UTIL.Mime_Header('text/xml');  
                                    --
                                    htp.p('Content-length: ' || to_char(dbms_lob.getlength(v_retval)));
                                    htp.p('Content-Disposition:  inline; filename="Just.xml"');
                                    owa_util.http_header_close;
                                    --
                                    v_blob := wwv_flow_utilities.clob_to_blob(v_retval);
                                    --
                                    wpg_docload.download_file(v_blob);
                                    --
                                  end;
                                  h1. Select Statement that is needed to be "UNION-ED" with the Select Statement of the previous procedure:
                                   
                                  select 
                                         xmlelement(
                                           "LISTINGS",
                                           xmlagg(
                                             xmlelement(
                                               "LISTING",
                                               xmlforest(
                                  'RR-'||R."RES_ID" as "Property_Ref_No",
                                  "UNIT_TYPE_LOOKUP"."UNIT_TYPE" as "Unit_Type",
                                  'Rent' as "Ad_Type",
                                  'Dubai' as "State",
                                  L.LOCATION_NAME_EN AS "Community",
                                  "PROJECTS"."PROJECT_NAME_EN" as "Property_Name",
                                  "RES_RENT"."ASKING_PRICE" as "Price",
                                  ' ' as "Frequency",
                                  ST."SUB_TYPE" as "Unit_Model",
                                  R."RES_SIZE" as "Unit_Builtup_Area",
                                  R.BEDS as "Bedrooms",
                                  R.bath as "No_of_Bathrooms",
                                  "RES_RENT"."AD_TITLE" as "Property_Title",
                                  "RES_RENT".DESCRIPTION as "Web_Remarks",
                                  to_char("RES_RENT"."UPDATED_ON",'YYYY-MM-DD HH:MI:SSPM') as "Last_Updated",
                                  R.VIDEO as "Web_Tour",
                                   (
                                  select xmlagg(
                                                            
                                                               xmlforest(
                                                              'http://thevillaproject.com/apex/erp/IMAGES/'||IMG.ID AS "ImageUrl"
                                                               
                                                             )
                                                          )
                                  FROM       
                                        IMAGES IMG      
                                  WHERE IMG.RES_ID = R.RES_ID ) Images ))))
                                        
                                  FROM 
                                        "RES_RENT" "RES_RENT"
                                  inner join
                                        RES R on (R."RES_ID"="RES_RENT"."RES_ID")
                                  inner join
                                            "UNIT_STATUS_LOOKUP" on (UNIT_STATUS_LOOKUP.status_id = "RES_RENT"."RES_STATUS")
                                  inner join
                                        "PROJECTS" "PROJECTS" on ("PROJECTS"."PROJECT_ID"=R."PROJECT_ID")
                                  inner join
                                        "UNIT_TYPE_LOOKUP" "UNIT_TYPE_LOOKUP" on ("UNIT_TYPE_LOOKUP"."TYPE_ID"=R."RES_TYPE")
                                  inner join
                                        "RES_SUB_TYPE_LOOKUP" ST on (ST."SUB_TYPE_ID"=R."SUB_TYPE")
                                  INNER JOIN 
                                        COMPLEX_LOOKUP CL ON (CL.COMPLEX_ID = "PROJECTS".COMPLEX_ID)
                                  INNER JOIN 
                                        LOCATIONS L ON (L.LOCATION_ID = CL.LOCATION_ID)
                                  
                                  WHERE  "RES_RENT".RES_status IN (5,8) AND "RES_RENT".LIVE = 'Y' AND "RES_RENT".DESCRIPTION IS NOT NULL
                                  • 14. Re: Query to generate Nested XML feed
                                    fac586
                                    Prabodh wrote:
                                    Not a bug.
                                    The Media Resource query must return only one row !
                                    My guess is that your query returns more than one rows, that is multiple XML lines.

                                    If you run the query in SQLWorkshop / SQL Developer you should get only ONE LINE output for Media Resource to work.
                                    It does only return 1 row. The problem is that despite <tt>xmlserialize</tt> returning a CLOB value, it errors out if the XML document exceeds the 4000 byte VARCHAR2 SQL column limit (it will return 9 listings, and fail on 10 or more). I'm not an APEX Listener user, but something similar has been previously reported: +{thread:id=2449844}+. This does appear to be a bug.

                                    As a workaround, create the web service template using the PL/SQL Block</tt> source type, and Re: XML returned from on-demand process.
                                    SQLX functions are complex and not easy to work with ;)
                                    Speak for yourself.

                                    Edited by: fac586 on 17-Dec-2012 11:01

                                    @Fateh: I see you've already discovered the buffered HTTP output method.
                                    1 2 Previous Next