0 Replies Latest reply: Feb 14, 2012 6:57 AM by 917352 RSS

    help xmlagg function

    917352
      i have this query:

      select XMLELEMENT(name "eeml",
      XMLAGG(XMLELEMENT(name "environment",XMLCONCAT(
      XMLELEMENT(name "title", a.codice),
      XMLELEMENT(name "data", XMLATTRIBUTES(x.data_id as "id"),
      XMLCONCAT(XMLELEMENT(name "tag", x.tag),
      XMLELEMENT(name "datapoints",
      (XMLELEMENT(name "value",round(avg(x.avg_data),2)))
      ),
      XMLELEMENT(name "unit", x.unit)

      )
      )
      )
      )

      )
      )

                          as xml
      from ambiente a, table(a.dati) d, XMLTABLE('for $d in //data return <dati>
      <id>{data($d/@id)}</id>
      <tag>{$d/tag}</tag>
      <unit>{$d/unit}</unit>
      <avg>{(data($d/max_value)+data($d/min_value)) div 2}</avg>
      </dati>' PASSING value(d).DatiXML
      COLUMNS data_id integer PATH '/dati/id',
      tag varchar2(20) PATH '/dati/tag',
      unit varchar2(20) PATH '/dati/unit',
      avg_data number(5) PATH '/dati/avg'

      ) x
      where to_char(value(d).giorno,'YYYY')='2011'
      group by a.codice,x.data_id,x.tag,x.unit, to_char(value(d).giorno,'MON')
      order by x.data_id


      that produce:


      <eeml>
           <environment>
                <title>1</title>
                <data id="2">
                     <tag>temperature</tag>
                     <datapoints>
                          <value>36</value> //avg january value
                     </datapoints>
                     <unit>Celsius</unit>
                </data>
           </environment>
           <environment>
                <title>1</title>
                <data id="0">
                     <tag>energy</tag>
                     <datapoints>
                          <value>38.5</value> //avg january value
                     </datapoints>
                     <unit>Kilowatt</unit>
                </data>
           </environment>
           <environment>
                <title>1</title>
                <data id="2">
                     <tag>temperature</tag>
                     <datapoints>
                          <value>36</value> //avg february value
                     </datapoints>
                     <unit>Celsius</unit>
                </data>
           </environment>
           <environment>
                <title>1</title>
                <data id="0">
                     <tag>energy</tag>
                     <datapoints>
                          <value>36</value> //avg february value
                     </datapoints>
                     <unit>Kilowatthour</unit>
                </data>
           </environment>
      </eeml>

      but i want to have

      <eeml>
           <environment>
                <title>1</title>
                <data id="2">
                     <tag>temperature</tag>
                     <datapoints>
                          <value>36</value> //avg january
                          <value>36</value> //avg february
                     </datapoints>
                     <unit>Celsius</unit>
                </data>
                <data id="0">
                     <tag>energy</tag>
                     <datapoints>
                          <value>38.5</value> //avg january
                          <value>36</value> //avg february
                     </datapoints>
                     <unit>Kilowatt</unit>
                </data>
           </environment>
      </eeml>

      i have tried to put a xmlagg before the xmlconcat but doesn't work. What is the solution? Thanks for helping