XMLAGG with analytic functions

From: Matt T <puskas.duck_at_gmail.com>
Date: Thu, 21 Jun 2007 09:43:30 -0000
Message-ID: <1182419010.427069.170250@w5g2000hsg.googlegroups.com>

Anybody else had issues with values from analytic functions disappearing when wrapped in an XMLAGG function?

Apologies for the noddy example!

SQL> select *
  2 from v$version;

BANNER

---

Oracle9i Enterprise Edition Release 9.2.0.6.0 - Production PL/SQL Release 9.2.0.6.0 - Production
CORE 9.2.0.6.0 Production
TNS for Solaris: Version 9.2.0.6.0 - Production NLSRTL Version 9.2.0.6.0 - Production

  1* select xmlelement("TOTAL_EMP", count(*) over() ) FROM emp SQL> / XMLELEMENT("TOTAL_EMP",COUNT(*)OVER())

---

<TOTAL_EMP>10</TOTAL_EMP>
<TOTAL_EMP>10</TOTAL_EMP>
<TOTAL_EMP>10</TOTAL_EMP>
<TOTAL_EMP>10</TOTAL_EMP>
<TOTAL_EMP>10</TOTAL_EMP>
<TOTAL_EMP>10</TOTAL_EMP>
<TOTAL_EMP>10</TOTAL_EMP>
<TOTAL_EMP>10</TOTAL_EMP>
<TOTAL_EMP>10</TOTAL_EMP>
<TOTAL_EMP>10</TOTAL_EMP>

  1* select xmlagg(xmlelement("TOTAL_EMP", count(*) over() ) ) FROM emp
SQL> / XMLAGG(XMLELEMENT("TOTAL_EMP",COUNT(*)OVER()))

---

<TOTAL_EMP></TOTAL_EMP><TOTAL_EMP></TOTAL_EMP><TOTAL_EMP></
TOTAL_EMP><TOTAL_EMP> Received on Thu Jun 21 2007 - 04:43:30 CDT