This discussion is archived
5 Replies Latest reply: Nov 21, 2012 7:51 PM by Mindmap RSS

SQL query to generate Nested XML

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

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>

 
  • 1. Re: SQL query to generate Nested XML
    Kim Berg Hansen Expert
    Currently Being Moderated
    Hi, Fateh

    One possible way is to use XML functions to create your XML.
    Using XML functions you can do the IMAGES as an XMLAGG subquery rather than join to the image table.

    Here's an example using SCOTT schema:
    SQL> select xmlelement(
      2            "ROWSET"
      3          , xmlagg(
      4               xmlelement(
      5                  "ROW"
      6                , xmlforest(
      7                     d.deptno as "ID"
      8                   , d.dname as "NAME"
      9                   , (
     10                        select xmlagg(
     11                                  xmlelement(
     12                                     "ImgUrl"
     13                                   , 'http://the.server.com/'||e.empno
     14                                  )
     15                                  order by e.empno
     16                               )
     17                          from scott.emp e
     18                         where e.deptno = d.deptno
     19                     ) as "Images"
     20                  )
     21               )
     22               order by d.deptno
     23            )
     24         ) the_xml
     25    from scott.dept d
     26    /* joins to the other tables EXCEPT image table */
     27  /
    
    THE_XML
    --------------------------------------------------------------------------------
    <ROWSET><ROW><ID>10</ID><NAME>ACCOUNTING</NAME><Images><ImgUrl>http://the.server
    That output is an XMLTYPE column (think of it as a CLOB with added functionality ;-) )
    My SQL*PLUS cuts the output, but believe me, it is all there.
    Just to show it, here's the same example wrapped in an XMLSERIALIZE function to pretty-print the XML:
    SQL> select xmlserialize(
      2            content
      3            xmlelement(
      4               "ROWSET"
      5             , xmlagg(
      6                  xmlelement(
      7                     "ROW"
      8                   , xmlforest(
      9                        d.deptno as "ID"
     10                      , d.dname as "NAME"
     11                      , (
     12                           select xmlagg(
     13                                     xmlelement(
     14                                        "ImgUrl"
     15                                      , 'http://the.server.com/'||e.empno
     16                                     )
     17                                     order by e.empno
     18                                  )
     19                             from scott.emp e
     20                            where e.deptno = d.deptno
     21                        ) as "Images"
     22                     )
     23                  )
     24                  order by d.deptno
     25               )
     26            )
     27            as varchar2(4000)
     28            indent size=2
     29         ) the_xml
     30    from scott.dept d
     31    /* joins to the other tables EXCEPT image table */
     32  /
    
    THE_XML
    --------------------------------------------------------------------------------
    <ROWSET>
      <ROW>
        <ID>10</ID>
        <NAME>ACCOUNTING</NAME>
        <Images>
          <ImgUrl>http://the.server.com/7782</ImgUrl>
          <ImgUrl>http://the.server.com/7839</ImgUrl>
          <ImgUrl>http://the.server.com/7934</ImgUrl>
        </Images>
      </ROW>
      <ROW>
        <ID>20</ID>
        <NAME>RESEARCH</NAME>
        <Images>
          <ImgUrl>http://the.server.com/7369</ImgUrl>
          <ImgUrl>http://the.server.com/7566</ImgUrl>
          <ImgUrl>http://the.server.com/7788</ImgUrl>
          <ImgUrl>http://the.server.com/7876</ImgUrl>
          <ImgUrl>http://the.server.com/7902</ImgUrl>
        </Images>
      </ROW>
      <ROW>
        <ID>30</ID>
        <NAME>SALES</NAME>
        <Images>
          <ImgUrl>http://the.server.com/7499</ImgUrl>
          <ImgUrl>http://the.server.com/7521</ImgUrl>
          <ImgUrl>http://the.server.com/7654</ImgUrl>
          <ImgUrl>http://the.server.com/7698</ImgUrl>
          <ImgUrl>http://the.server.com/7844</ImgUrl>
          <ImgUrl>http://the.server.com/7900</ImgUrl>
        </Images>
      </ROW>
      <ROW>
        <ID>40</ID>
        <NAME>OPERATIONS</NAME>
      </ROW>
    </ROWSET>
    For a webservice you do not need to pretty-print the XML that is returned by the webservice.

    I do not know APEX, so I do not know if APEX supports exposing an allready built piece of XML rather than exposing a query result.
    But my guess is that it should do it very nicely if you query an XMLTYPE datatype (that is - use the first of my examples rather than the pretty-printed one.)

    If you can't get APEX to do it this way, then I suggest you try asking in the APEX forum rather than the SQL forum ;-)
  • 2. Re: SQL query to generate Nested XML
    Stew Ashton Expert
    Currently Being Moderated
    odie_63 showed a feature of XMLAGG that I think is neat, even though I can't see where it's documented:

    If you use GROUP BY, you can use XMLAGG once to get an XML fragment per group, then use XMLAGG again to combine the fragments into one.

    So you are actually aggregating twice. You can even have two ORDER BY expressions! I find this surprising, semantically speaking.

    Anyway, this is a good candidate for nested XMLAGG, which means you don't have to use repeated subqueries:
    select xmlserialize(content
    xmlelement(
      "ROWSET",
      xmlagg(
        xmlelement(
          "ROW",
          xmlforest(
            deptno ID,
            dname NAME,
            xmlagg(
              xmlelement("ImgUrl", 'http://the.server.com/'||empno)
              order by empno
            ) "Images"
          )
        )
        order by deptno
      )
    ) indent)
    the_xml
    from scott.dept join scott.emp using(deptno)
    group by deptno, dname;
  • 3. Re: SQL query to generate Nested XML
    Kim Berg Hansen Expert
    Currently Being Moderated
    Neat, Stew, thanks. (And thanks to odie, the grand master of XML ;-) )

    Actually the reason it works is like explained in this thread on nesting aggregates: {thread:id=2457004}

    The documentation [url http://docs.oracle.com/cd/E11882_01/server.112/e26088/functions003.htm#i89203]here has a small example of nesting aggregates - but I think many people (including me) misses that small bit in the docs ;-)
  • 4. Re: SQL query to generate Nested XML
    Stew Ashton Expert
    Currently Being Moderated
    Thanks for that explanation, Kim. I kept looking for an explanation for XMLAGG instead of searching on nested aggregations in general.

    I checked out LISTAGG, and it too allows for ORDER BY on both levels:
    SELECT LISTAGG(
      DNAME || ';' || LISTAGG(ENAME, ',') WITHIN GROUP(ORDER BY ENAME)
      , '|'
    )
    within group (order by dname) names
    FROM SCOTT.DEPT JOIN SCOTT.EMP USING(DEPTNO)
    group by dname;
    
    ACCOUNTING;CLARK,KING,MILLER|RESEARCH;ADAMS,FORD,JONES,SCOTT,SMITH|SALES;ALLEN,BLAKE,JAMES,MARTIN,TURNER,WARD
  • 5. Re: SQL query to generate Nested XML
    Mindmap Pro
    Currently Being Moderated
    Thanks Kim,

Legend

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