| rowconcatenate of clob [message #459157] |
Thu, 03 June 2010 11:16  |
b_52globemaster
Messages: 51
Registered: July 2005
|
Member |
|
|
Hi,
late consider i'm having a table like that:
ID | XMLCONTENT
--------------------------
1 | <delta>1</delta>
1 | <gamma>2</gamma>
1 | <omega>3</omega>
1 | <star>4</star>
the XMLCONTENT is a CLOB
any one have a function that concatenate the rows of a clob,
like the final result should be like:
ID | XMLCONTENT
-----------------------------------------------------------------
1 | <delta>1</delta><gamma>2</gamma><omega>3</omega><star>4
| </star>
-----------------------------------------------------------------
and thanks
|
|
|
|
| Re: rowconcatenate of clob [message #459159 is a reply to message #459157] |
Thu, 03 June 2010 11:30  |
Solomon Yakobson
Messages: 3303
Registered: January 2010
Location: Connecticut, USA
|
Senior Member |
|
|
And your Oracle version is? In any case, if you are on 11.2 use LISTAGG, otherwise CLOBAGG would be your best bet. Search forum/net for user defined STRAGG function and create exactly same for CLOB.
SY.
|
|
|
|
|
|
| Re: rowconcatenate of clob [message #459169 is a reply to message #459161] |
Thu, 03 June 2010 12:00 |
Solomon Yakobson
Messages: 3303
Registered: January 2010
Location: Connecticut, USA
|
Senior Member |
|
|
Actually you could use XMLAGG:
with t as (
select 1 id ,to_clob('<delta>1</delta>') xmlcontent from dual union all
select 1,to_clob('<gamma>2</gamma>') from dual union all
select 1,to_clob('<omega>3</omega>') from dual union all
select 1,to_clob('<star>4</star>') from dual
)
select replace(
xmlagg(
xmltype(
xmlcontent
)
),
chr(10)
) xmlcontent
from t
/
XMLCONTENT
--------------------------------------------------------------------------------
<delta>1</delta><gamma>2</gamma><omega>3</omega><star>4</star>
SQL>
Issue is your source does not have any columns to identify aggregation order. So if table rows will be read in adifferent order you will get something like:
SQL> with t as (
2 select 1 id ,to_clob('<delta>1</delta>') xmlcontent from dual union all
3 select 1,to_clob('<omega>3</omega>') from dual union all
4 select 1,to_clob('<star>4</star>') from dual union all
5 select 1,to_clob('<gamma>2</gamma>') from dual
6 )
7 select replace(
8 xmlagg(
9 xmltype(
10 xmlcontent
11 )
12 ),
13 chr(10)
14 ) xmlcontent
15 from t
16 /
XMLCONTENT
--------------------------------------------------------------------------------
<delta>1</delta><omega>3</omega><star>4</star><gamma>2</gamma>
SQL>
SY.
|
|
|
|
|
|
| Re: rowconcatenate of clob [message #459439 is a reply to message #459157] |
Sat, 05 June 2010 05:58 |
b_52globemaster
Messages: 51
Registered: July 2005
|
Member |
|
|
hm can't use xmlagg in my case ....
@Michel ,
sorry to abuse i have been fetching for yu post, but no success in finding the function that concatenate the rows of clob.
could yu give me the direct link please
sorry to disturb
|
|
|
|
] 
Blog
Search
Help
Members
Register
Login
Home
