Home » Developer & Programmer » JDeveloper, Java & XML » rowconcatenate of clob
rowconcatenate of clob [message #459157] Thu, 03 June 2010 11:16 Go to next message
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 Go to previous messageGo to next message
Solomon Yakobson
Messages: 3269
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 #459161 is a reply to message #459159] Thu, 03 June 2010 11:37 Go to previous messageGo to next message
Michel Cadot
Messages: 68624
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
I think I posted here one for CLOB not so long ago (about a month).

Regards
Michel
Re: rowconcatenate of clob [message #459169 is a reply to message #459161] Thu, 03 June 2010 12:00 Go to previous messageGo to next message
Solomon Yakobson
Messages: 3269
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 #459327 is a reply to message #459169] Fri, 04 June 2010 07:00 Go to previous messageGo to next message
b_52globemaster
Messages: 51
Registered: July 2005
Member
thanks
Re: rowconcatenate of clob [message #459439 is a reply to message #459157] Sat, 05 June 2010 05:58 Go to previous message
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
Previous Topic: xmlelement inside an xmlement !
Next Topic: help with clob concatenation
Goto Forum:
  


Current Time: Thu Mar 28 06:39:54 CDT 2024