This discussion is archived
5 Replies Latest reply: Jan 23, 2012 10:30 AM by Andrei Costache, Oracle RSS

Another nested xmlagg

902809 Newbie
Currently Being Moderated
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 Newbie
    Currently Being Moderated
    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 Guru
    Currently Being Moderated
    post sample data plz
  • 3. Re: Another nested xmlagg
    902809 Newbie
    Currently Being Moderated
    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 Guru
    Currently Being Moderated
    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 Journeyer
    Currently Being Moderated
    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

Legend

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