This discussion is archived
1 2 Previous Next 15 Replies Latest reply: Dec 17, 2012 1:28 PM by fac586 RSS

Query to generate Nested XML feed

Mindmap Pro
Currently Being Moderated
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 Guru
    Currently Being Moderated
    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 Pro
    Currently Being Moderated
    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 Pro
    Currently Being Moderated
    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 Guru
    Currently Being Moderated
    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 Pro
    Currently Being Moderated
    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 Guru
    Currently Being Moderated
    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 Pro
    Currently Being Moderated
    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 Guru
    Currently Being Moderated
    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 Pro
    Currently Being Moderated
    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 Guru
    Currently Being Moderated
    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 Pro
    Currently Being Moderated
    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 Guru
    Currently Being Moderated
    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 Pro
    Currently Being Moderated
    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 Guru
    Currently Being Moderated
    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

Legend

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