stragg in 10gR2

well, you all know string aggregration

here is a suggestion with xquery in 10gR2

select deptno,
  replace( replace( replace(
    XMLQUERY(‘for $cc in ora:view(“emp”) let $ename:=$cc/ROW/ENAME/text()     where $cc/ROW/DEPTNO/number()=$deptno/d/number() return <e>{$ename}</e>’
    passing by value xmltype(‘<d>’||deptno||'</d>’) as “deptno”
    returning content
  ),'</e><e>’, ‘,’),'<e>’),'</e>’) enames
from dept

—— ——————————————

Published by

Laurent Schneider

Oracle Certified Master

6 thoughts on “stragg in 10gR2”

  1. neato !

    What do I need to do to make this work ? (XE/Linux)

    ORA-19114: error during parsing the XQuery expression:
    ORA-06550: line 1, column 13:
    PLS-00201: identifier ‘SYS.DBMS_XQUERYINT’ must be declared
    ORA-06550: line 1, column 7:
    PL/SQL: Statement ignored

  2. great anonymous comment 😉
    thanks a lot for this one!

    select deptno,substr(replace(replace(XMLAgg(XMLElement(“x”,ename)),'</x>’),'<x>’,’,’),2) enames from emp group by deptno
    ———- —————————————-
    40 SMITH

  3. Hi Laurent,

    You might be interested in a string aggregation technique I came up with recently that uses a reference model. You can find it at .

    I’ve also got a few pages that describe other popular techniques (along with performance metrics to compare them) starting at . I wanted to include your XQuery approach (looks like a sharp one BTW) on my site, but unfortunately I only have XE so I couldn’t test it. I included a link to your blog post about it though at the end of my string aggregation section.

Leave a Reply