5 Replies Latest reply: Jan 23, 2012 12:30 PM by Andrei Costache, Oracle RSS

    Another nested xmlagg

    902809
      Hi folks,

      I have the following query:


      select xmlroot(
           xmlelement
           (
                EMSData,
      XMLForest(
      v_DailyStartDate as "DailyStartDate",
      v_WeeklyStartDate as "WeeklyStartDate",
      v_MonthlyStartDate as "MonthlyStartDate"
      ),

                XMLAGG
                (

                     XMLELEMENT
                     (
                          year,
                          xmlattributes(calyear),
                          xmlagg
                          (
                               xmlelement
                               (
                                    interval,
                                    xmlattributes(intervalType as "Type",intervalValue as "Value"),
                                    xmlelement
                                    (
                                         data,
                                         xmlattributes(provider),
                                         xmlforest
                                         (
                                              bytes,
                                              files
                                         )
                                    )
                               )
                               order by orderBy,intervalvalue desc
                          )
                     )
                     order by calyear desc
                )
           ), version '1.0').getClobVal() into l_xml
      from myTable
      group by calyear;

      Which produces the following XML:
      <?xml version="1.0"?>
      <EMSDATA>
      <DailyStartDate>30-SEP-11</DailyStartDate>
      <WeeklyStartDate>01-OCT-08</WeeklyStartDate>
      <MonthlyStartDate>01-JAN-02</MonthlyStartDate>
      <YEAR CALYEAR="2011">
      <INTERVAL Type="DAY" Value="333">
      <DATA PROVIDER="ALPHA">
      <BYTES>16734642488</BYTES>
      <FILES>2333</FILES>
      </DATA>
      </INTERVAL>
      <INTERVAL Type="DAY" Value="333">
      <DATA PROVIDER="BETA">
      <BYTES>2580914653</BYTES>
      <FILES>8148</FILES>
      </DATA>
      </INTERVAL>
      <INTERVAL Type="DAY" Value="332">
      <DATA PROVIDER="ALPHA">
      <BYTES>24516086977</BYTES>
      <FILES>2315</FILES>
      </DATA>
      </INTERVAL>
      <INTERVAL Type="DAY" Value="332">
      <DATA PROVIDER="BETA">
      <BYTES>1090547502078</BYTES>
      <FILES>16548</FILES>
      </DATA>
      </INTERVAL>
      </YEAR>
      </EMSDATA>

      What I really want the XML to look like is:

      <?xml version="1.0"?>
      <EMSDATA>
      <DailyStartDate>30-SEP-11</DailyStartDate>
      <WeeklyStartDate>01-OCT-08</WeeklyStartDate>
      <MonthlyStartDate>01-JAN-02</MonthlyStartDate>
      <YEAR CALYEAR="2011">
      <INTERVAL Type="DAY" Value="333">
      <DATA PROVIDER="ALPHA">
      <BYTES>16734642488</BYTES>
      <FILES>2333</FILES>
      </DATA>
      <DATA PROVIDER="BETA">
      <BYTES>2580914653</BYTES>
      <FILES>8148</FILES>
      </DATA>
      </INTERVAL>
      <INTERVAL Type="DAY" Value="332">
      <DATA PROVIDER="ALPHA">
      <BYTES>24516086977</BYTES>
      <FILES>2315</FILES>
      </DATA>
      <DATA PROVIDER="BETA">
      <BYTES>1090547502078</BYTES>
      <FILES>16548</FILES>
      </DATA>
      </INTERVAL>
      </YEAR>
      </EMSDATA>


      I know it is something simple but I'm brain fried.

      Please advise.

      Best,
      Nat
        • 1. Re: Another nested xmlagg
          902809
          I'm thinking I need a nested select statement such as:
          select xmlroot
          (
               xmlelement
               (
                    EMSData,
                    XMLForest
                    (
                         v_DailyStartDate as "DailyStartDate",
                         v_WeeklyStartDate as "WeeklyStartDate",
                         v_MonthlyStartDate as "MonthlyStartDate"
                    ),

                    XMLAGG
                    (

                         XMLELEMENT
                         (
                              year,
                              xmlattributes(calyear),
                              (
                                   select
                                   xmlagg
                                   (
                                        xmlelement
                                        (
                                             interval,
                                             xmlattributes(intervalType as "Type",intervalValue as "Value"),
                                             xmlelement
                                             (
                                                  data,xmlattributes(provider),
                                                  xmlelement
                                                  (
                                                       "BYTES", bytes
                                                  ),
                                                  xmlelement
                                                  (
                                                       "FILES",files
                                                  )
                                             )
                                        )
                                   ) from mytable
                              )
               
                         )
                         order by calyear desc
                    )
               ),
               version '1.0'
          ).getClobVal() into l_xml
          from mytable
          group by calyear;

          However, when I try this Oracle just sits there. So, I decided to just try the nested sql itself to see the results. I ran the following:

          declare

          l_xml clob;

          myFilename varchar2(50) := 'myTestXML';

          v_ctx DBMS_XMLGen.ctxHandle;
          xmldoc dbms_xmldom.DOMDocument;


          begin


          select
          xmlroot(
                                   xmlagg
                                   (
                                        xmlelement
                                        (
                                             interval,
                                             xmlattributes(intervalType as "Type",intervalValue as "Value"),
                                             xmlelement
                                             (
                                                  data,xmlattributes(provider),
                                                  xmlforest
                                                  (
                                                       bytes,
                                                       files
                                                  )
                                             )
                                        )
                                   ) ,
               version '1.0'
          ).getClobVal() into l_xml
          from mytable;



          xmldoc := dbms_xmldom.newDOMDocument(l_xml);


          dbms_xmldom.writeToFile(xmldoc, 'ERR_DIR'||'/'||myFilename||'.xml');

          dbms_xmldom.freeDocument(xmldoc);


          end;


          I get this error:


          ERROR at line 1:
          ORA-31011: XML parsing failed
          ORA-19202: Error occurred in XML processing
          LPX-00245: extra data after end of document
          Error at line 8
          ORA-06512: at "XDB.DBMS_XMLDOM", line 5345
          ORA-06512: at "XDB.DBMS_XMLDOM", line 5370
          ORA-06512: at "XDB.DBMS_XMLDOM", line 5290
          ORA-06512: at line 42

          Can anyone offer insight as to what I'm doing wrong.

          Best,
          Nat

          Edited by: 899806 on Dec 1, 2011 9:15 AM

          Edited by: 899806 on Dec 1, 2011 1:45 PM
          • 2. Re: Another nested xmlagg
            AlexAnd
            post sample data plz
            • 3. Re: Another nested xmlagg
              902809
              myTable
              CALYEAR                                                                                                    NOT NULL CHAR(4)
              INTERVALTYPE                                                                                                    NOT NULL VARCHAR2(5)
              INTERVALVALUE                                                                                                    NOT NULL VARCHAR2(3)
              BYTES                                                                                                         NOT NULL NUMBER
              FILES                                                                                                         NOT NULL NUMBER
              ORDERBY                                                                                                              CHAR(1)
              PROVIDER                                                                                                              VARCHAR2(50)


              CALYEAR INTERVALTYPE INTERVALVALUE BYTES FILES PROVIDER
              2011 DAY 325 2.1289E+11     7858 ALPHA
              2011 DAY 326 2.1097E+11 10357 ALPHA
              2011 DAY 327 1834573530     4245 ALPHA
              2011 DAY 328 1820872933     4237 ALPHA
              2011 DAY 329 1361347941     2938 ALPHA
              2011 DAY 330     4735248     12 ALPHA
              2011 DAY 332 4487482396     9349 ALPHA
              2011 DAY 333 2580914653     8148 ALPHA
              2011 WEEK 01 4.1421E+10 32628 ALPHA
              2011 WEEK 02 4.9055E+10 32932 ALPHA
              2011 WEEK 03 4.4156E+10 30666 ALPHA
              2006 MONTH 01 1.7454E+12 87217 ALPHA
              2006 MONTH 02 1.2735E+12 70587 ALPHA
              2006 MONTH 03 2.2809E+12 98663 ALPHA
              2006 MONTH 04 2.1725E+12 94048 ALPHA
              2006 MONTH 05 2.6159E+12 111529 ALPHA
              2006 MONTH 06 1.3450E+12 87896 ALPHA
              2006 MONTH 07 1.4917E+12 167626 ALPHA
              2011 DAY 325 2.1289E+11     7858 BETA
              2011 DAY 326 2.1097E+11 10357 BETA
              2011 DAY 327 1834573530     4245 BETA
              2011 DAY 328 1820872933     4237 BETA
              2011 DAY 329 1361347941     2938 BETA
              2011 DAY 330     4735248     12 BETA
              2011 DAY 332 4487482396     9349 BETA
              2011 DAY 333 2580914653     8148 BETA
              2011 WEEK 01 4.1421E+10 32628 BETA
              2011 WEEK 02 4.9055E+10 32932 BETA
              2011 WEEK 03 4.4156E+10 30666 BETA
              2006 MONTH 01 1.7454E+12 87217 BETA
              2006 MONTH 02 1.2735E+12 70587 BETA
              2006 MONTH 03 2.2809E+12 98663 BETA
              2006 MONTH 04 2.1725E+12 94048 BETA
              2006 MONTH 05 2.6159E+12 111529 BETA
              2006 MONTH 06 1.3450E+12 87896 BETA
              2006 MONTH 07 1.4917E+12 167626 BETA
              • 4. Re: Another nested xmlagg
                AlexAnd
                from
                WITH mytable AS
                  (SELECT 2011 calyear,
                    'DAY' intervaltype,
                    333 intervalvalue,
                    2580914653 bytes,
                    8148 files ,
                    'ALPHA' provider
                  FROM dual
                  UNION ALL
                  SELECT 2011, 'DAY', 333, 2580914653, 8148, 'BETA' FROM dual
                  )
                select 
                xmlagg (
                xmlelement ( "INTERVAL", xmlattributes(intervaltype as "Type",intervalvalue as "Value"), 
                xmlelement ("DATA", xmlattributes(provider), xmlforest ( bytes, files ) ))
                )
                from mytable
                group by intervaltype, intervalvalue
                
                <INTERVAL Type="DAY" Value="333"><DATA PROVIDER="ALPHA"><BYTES>2580914653</BYTES><FILES>8148</FILES></DATA></INTERVAL><INTERVAL Type="DAY" Value="333"><DATA PROVIDER="BETA"><BYTES>2580914653</BYTES><FILES>8148</FILES></DATA></INTERVAL>
                to
                WITH mytable AS
                  (SELECT 2011 calyear,
                    'DAY' intervaltype,
                    333 intervalvalue,
                    2580914653 bytes,
                    8148 files ,
                    'ALPHA' provider
                  FROM dual
                  UNION ALL
                  SELECT 2011, 'DAY', 333, 2580914653, 8148, 'BETA' FROM dual
                  )
                select 
                xmlelement ( "INTERVAL", xmlattributes(intervaltype as "Type",intervalvalue as "Value"), 
                xmlagg (
                xmlelement ("DATA", xmlattributes(provider), xmlforest ( bytes, files ) ))
                )
                from mytable
                group by intervaltype, intervalvalue 
                  
                <INTERVAL Type="DAY" Value="333"><DATA PROVIDER="ALPHA"><BYTES>2580914653</BYTES><FILES>8148</FILES></DATA><DATA PROVIDER="BETA"><BYTES>2580914653</BYTES><FILES>8148</FILES></DATA></INTERVAL>
                so change your query for your needs
                • 5. Re: Another nested xmlagg
                  Andrei Costache, Oracle
                  Hi Nat,

                  This is the Oracle Berkeley DB XML (BDB XML or DBXML) forum. You were likely looking to post your question on the Oracle XML DB (XMLDB) forum, XML DB.

                  Regards,
                  Andrei