1 2 Previous Next 15 Replies Latest reply: Dec 17, 2012 3:28 PM by fac586 Go to original post RSS
      • 15. Re: Query to generate Nested XML feed
        fac586
        Fateh wrote:
        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:
        What is the problem? That you can't UNION queries returning CLOBs or ADTs?
        select
            xmlserialize(
                document
                xmlelement(
                    "doc"
                  , xmlagg(
                        xmlelement(
                            "row"
                          , xmlconcat(
                                xmlelement("id", empno)
                              , xmlelement("name", ename))))))
        from
            emp
        union
        select
            xmlserialize(
                document
                xmlelement(
                    "doc"
                  , xmlagg(
                        xmlelement(
                            "row"
                          , xmlconcat(
                                xmlelement("id", deptno)
                              , xmlelement("name", dname))))))
        from
            dept;
        
        
        ORA-00932: inconsistent datatypes: expected - got CLOB
        Using UNION ALL avoids this (assuming that there are no duplicate rows, or you don't care about duplicate rows):
        select
            xmlserialize(
                document
                xmlelement(
                    "doc"
                  , xmlagg(
                        xmlelement(
                            "row"
                          , xmlconcat(
                                xmlelement("id", empno)
                              , xmlelement("name", ename)))))) xml
        from
            emp
        where
            job = 'ANALYST'
        union all
        select
            xmlserialize(
                document
                xmlelement(
                    "doc"
                  , xmlagg(
                        xmlelement(
                            "row"
                          , xmlconcat(
                                xmlelement("id", deptno)
                              , xmlelement("name", dname))))))
        from
            dept;
        
        XML
        ---
        <doc><row><id>7902</id><name>FORD</name></row><row><id>7521</id><name>WARD</name></row><row><id>8000</id><name>KOVACS</name></row></doc>
        <doc><row><id>10</id><name>ACCOUNTING</name></row><row><id>20</id><name>RESEARCH</name></row><row><id>30</id><name>SALES</name></row><row><id>40</id><name>OPERATIONS</name></row></doc>
        However this returns 2 rows/documents where presumably you want the results combined into a single document. In which case, there are 2 possibilities.

        1. If the 2 queries return an equivalent projection (number and type of columns) and generate the same XML elements, then perform the UNION/UNION ALL in a common table expression or in-line view prior to generating the XML:
        with t as (
          select
              empno id
            , ename name
          from
              emp
          where
              job = 'ANALYST'
          union all
          select
              deptno
            , dname
          from
              dept)
        select
            xmlserialize(
                document
                xmlelement(
                    "doc"
                  , xmlagg(
                        xmlelement(
                            "row"
                          , xmlconcat(
                                xmlelement("id", id)
                              , xmlelement("name", name)))))) xml
        from
            t;
        
        XML
        ---
        <doc><row><id>7902</id><name>FORD</name></row><row><id>7521</id><name>WARD</name></row><row><id>8000</id><name>KOVACS</name></row><row><id>10</id><name>ACCOUNTING</name></row><row><id>20</id><name>RESEARCH</name></row><row><id>30</id><name>SALES</name></row><row><id>40</id><name>OPERATIONS</name></row></doc>
        2. If the 2 queries return different projections and/or generate different XML elements, you have to UNION the XML documents and perform an additional XML aggregation in an outer query:
        with x as (
          select
                  xmlelement(
                      "emps"
                    , xmlagg(
                          xmlelement(
                              "emp"
                            , xmlconcat(
                                  xmlelement("id", empno)
                                , xmlelement("name", ename)
                                , xmlelement("sal", sal))))) xml
          from
              emp
          where
              job = 'ANALYST'
          union all
          select
                  xmlelement(
                      "depts"
                    , xmlagg(
                          xmlelement(
                              "dept"
                            , xmlconcat(
                                  xmlelement("id", deptno)
                                , xmlelement("name", dname)))))
          from
              dept)
        select
            xmlserialize(
                document
                xmlelement(
                    "doc"
                  , xmlagg(xml)))
        from
            x;
        
        XML
        ---
        <doc><emps><emp><id>7902</id><name>FORD</name><sal>3000</sal></emp><emp><id>7521</id><name>WARD</name><sal>1250</sal></emp><emp><id>8000</id><name>KOVACS</name><sal>2000</sal></emp></emps><depts><dept><id>10</id><name>ACCOUNTING</name></dept><dept><id>20</id><name>RESEARCH</name></dept><dept><id>30</id><name>SALES</name></dept><dept><id>40</id><name>OPERATIONS</name></dept></depts></doc>
        1 2 Previous Next