OraFAQ Forum: SQL & PL/SQL

Home » SQL & PL/SQL » SQL & PL/SQL » XMLAGG issue (Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production)

Show: Today's Messages :: Polls :: Message Navigator
E-mail to friend

XMLAGG issue [message #649793]

Wed, 06 April 2016 05:43

Xandot
Messages: 235
Registered: January 2014
Location: India

Senior Member

Hello All,

Example:

with emp as 
(select 'KING' ename from dual
union
select 'BLAKE' ename from dual
union
select 'CLARK' ename from dual
union
select 'JONES' ename from dual
union
select 'SCOTT' ename from dual
union
select 'SMITH' ename from dual
union
select 'MARTIN' ename from dual) 
select 
   rtrim (xmlagg (xmlelement (e, ename || ',')).extract ('//text()'), ',') enames
from emp

above code return me output like:

BLAKE,CLARK,JONES,KING,MARTIN,SCOTT,SMITH

but I need output like:

'BLAKE','CLARK','JONES','KING','MARTIN','SCOTT','SMITH'

when I added concatenation then its showing output like:
'BLAKE','CLARK','JONES','KING','MARTIN',&am p;am p;apos;SCOTT','SMITH'

previously I used "LISTAGG" function for the same and its showing output
corretly but my variable length would be more than 4000 char then it will be showing error message that's why I'm using "xmlagg".

could you please help me out?

Thanks,
Xandot

[Updated on: Wed, 06 April 2016 05:44]

Report message to a moderator

Re: XMLAGG issue [message #649794 is a reply to message #649793]

Wed, 06 April 2016 06:46

Michel Cadot
Messages: 68641
Registered: March 2007
Location: Nanterre, France, http://...

Senior Member
Account Moderator

SQL> set define off
SQL> with emp as
  2  (select 'KING' ename from dual
  3  union
  4  select 'BLAKE' ename from dual
  5  union
  6  select 'CLARK' ename from dual
  7  union
  8  select 'JONES' ename from dual
  9  union
 10  select 'SCOTT' ename from dual
 11  union
 12  select 'SMITH' ename from dual
 13  union
 14  select 'MARTIN' ename from dual)
 15  select
 16    replace(rtrim (xmlagg (xmlelement (e, '''' || ename || ''',')).extract ('//text()'), ','),
 17            '&apos;','''') enames
 18  from emp
 19  /
ENAMES
-------------------------------------------------------------------------------------------------
'BLAKE','CLARK','JONES','KING','MARTIN','SCOTT','SMITH'

1 row selected.

Quote:

my variable length would be more than 4000 char

http://www.dba-village.com/village/dvp_forum.OpenThread?ThreadIdA=78622#216019

Report message to a moderator

Re: XMLAGG issue [message #649796 is a reply to message #649794]

Wed, 06 April 2016 06:50

Xandot
Messages: 235
Registered: January 2014
Location: India

Senior Member

Thanks Michel.

Report message to a moderator

Re: XMLAGG issue [message #649810 is a reply to message #649796]

Wed, 06 April 2016 19:25

Solomon Yakobson
Messages: 3273
Registered: January 2010
Location: Connecticut, USA

Senior Member

EXTRACT is deprecated:

In this particular case all you need is XMLCAST:

with emp as
(select 'KING' ename from dual
union
select 'BLAKE' ename from dual
union
select 'CLARK' ename from dual
union
select 'JONES' ename from dual
union
select 'SCOTT' ename from dual
union
select 'SMITH' ename from dual
union
select 'MARTIN' ename from dual)
select rtrim(xmlcast(xmlagg(xmlelement(e, ename || ',')) as clob),',') enames
from emp
/

ENAMES
-----------------------------------------
BLAKE,CLARK,JONES,KING,MARTIN,SCOTT,SMITH

SQL>

SY.

Report message to a moderator

Previous Topic:	Query Rewriting - Assistance Needed
Next Topic:	difference between "child rows" and "siblings rows "

Goto Forum:

-=] Back to Top [=-

[ Syndicate this forum (XML) ] [

]

Current Time: Fri Apr 19 04:37:45 CDT 2024