5 Replies Latest reply: Nov 21, 2012 9:51 PM by Mindmap RSS

    SQL query to generate Nested XML

    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>
      
       
        • 1. Re: SQL query to generate Nested XML
          Kim Berg Hansen
          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
            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
              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
                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
                  Thanks Kim,