Limiting rows with rownum and XMLAGG
Hi there,
Using Oracle 9.2.0.6 on AIX...
The following query works... however, it seems the sorting is out of whack... most likely because of how Oracle handles rownum and order by in a query:
select XMLAgg(XMLElement("item",
XMLElement("title", ccnews.title ),
XMLElement("link", concat('link',ccnews.newsID) ),
XMLElement("description" , '<![CDATA[' || ccnews.description || ']]>' ),
(SELECT xmlagg(xmlelement("category", ccnews_category.category)) FROM ccnews_category, ccnews_category_rel
WHERE ccnews_category.languageid = '1'
AND ccnews_category_rel.languageid = '1' AND ccnews_category.newscategoryid = ccnews_category_rel.newscategoryid
AND ccnews_category_rel.newsid = ccnews.newsid),
XMLElement("guid", XMLAttributes('true' AS "isPermaLink"), concat('link',ccnews.newsID) ),
XMLElement("pubDate", to_char(FROM_TZ(CAST(ccnews.EMBARGODATE AS TIMESTAMP), DBTIMEZONE) AT TIME ZONE 'GMT', 'Dy, DD Mon YYYY HH24:MI:SS TZD')))).getClobVal() FROM ccnews WHERE rownum <= 200 ORDER BY CCNews.embargoDate DESC;
Trying to oder by ccnews.embargodate, but the items aren't properly ordered... how could I implement this query and limit the rowcount, if I cannot use rownum? I know dbms_xmlgen as a proc to set maximum rows, but I need to set some xml attributes for certain elements... (generating an RSS feed)
Any ideas or advice is greatly appreciated!
Message was edited by:
pl_sequel