Skip to Main Content
  • Questions
  • Merging all rows specific to a keyvalue

Breadcrumb

May 4th

Question and Answer

Chris Saxon

Thanks for the question, Ankit.

Asked: March 27, 2023 - 2:41 pm UTC

Last updated: March 29, 2023 - 5:29 pm UTC

Version: 20.2.0

Viewed 1000+ times

You Asked

-- Create table

create table test1 as
(
 select 2001 as keyvalue, 1 as counter, '+++ Input: My Name is ABC' as text from dual
 union all 
 select 2001 as keyvalue, 2 as counter, '+++ Input: Cricket is my favourite sport' as text from dual
 union all 
 select 2001 as keyvalue, 3 as counter, '+++ Input 23/05/23: Problem Summary This is test scenario' as text from dual
 union all 
 select 2001 as keyvalue, 4 as counter, '+++ Input: I love travelling' as text from dual
 );
 
 select * from test1;


---------------------------------------------

I have 3 columns:

KEYVALUE  |  COUNTER  |  TEXT
--------------------------------------------------------------------
2001      |    1      | +++ Input: My Name is ABC
--------------------------------------------------------------------
2001      |    2      | +++ Input: Cricket is my favourite sport
--------------------------------------------------------------------
2001      |    3      | +++ Input 23/05/23: Problem Summary This is test scenario
---------------------------------------------------------------------
2001      |    4      |+++ Input: I love travelling
---------------------------------------------------------------------


1) required output:

KEYVALUE  |  TEXT
---------------------------------------------------------------------
2001      |  +++ Input: My Name is ABC +++ Input: Cricket is my favourite sport +++ Input 23/05/23: Problem Summary This is test scenario +++ Input: I love travelling


Please note LISTAGG is not working due to the size.( I am trying to have a merged column of clob datatype)

I have made use of the below query:

SELECT
   keyvalue,
   DBMS_XMLGEN.CONVERT (
     EXTRACT(
       xmltype('<?xml version="1.0"?><document>' ||
               XMLAGG(
                 XMLTYPE('<V>' || DBMS_XMLGEN.CONVERT(text)||' '|| '</V>')
                 order by counter).getclobval() || '</document>'),
               '/document/V/text()').getclobval(),1) AS data_value
FROM cust_input_contents
 where keyvalue in ('2001')
GROUP BY keyvalue;


But I am getting the below error:

ORA-31011: XML parsing failed
ORA-19202: Error occurred in XML processing
LPX-00217: invalid character 8 (U+0008)
Error at line 3
ORA-06512: at "SYS.XMLTYPE", line 310
ORA-06512: at line 1
31011. 00000 -  "XML parsing failed"
*Cause:    XML parser returned an error while trying to parse the document.
*Action:   Check if the document to be parsed is valid.


2)Let's say after the merge I now have

KEYVALUE  |  TEXT
---------------------------------------------------------------------
2001      |  +++ Input: My Name is ABC +++ Input: Cricket is my favourite sport +++ Input 23/05/23: Problem Summary This is test scenario +++ Input: I love travelling


I now want to remove the entire line starting from Input when I encounter a problem summary in the text.

Required Output:

+++ Input: My Name is ABC +++ Input: Cricket is my favourite sport +++ +++ Input: I love travelling


Please note the total delimiters won't get affected (in this case +++), this is because I count the total input based on this delimter count.


and Chris said...

I'm unable to reproduce the error you get with the XML query:

SELECT
   keyvalue,
   DBMS_XMLGEN.CONVERT (
     EXTRACT(
       xmltype('<?xml version="1.0"?><document>' ||
               XMLAGG(
                 XMLTYPE('<V>' || DBMS_XMLGEN.CONVERT(text)||' '|| '</V>')
                 order by counter).getclobval() || '</document>'),
               '/document/V/text()').getclobval(),1) AS data_value
FROM  test1
where keyvalue in ('2001')
GROUP BY keyvalue;

  KEYVALUE DATA_VALUE                                                                      
---------- --------------------------------------------------------------------------------
      2001 +++ Input: My Name is ABC +++ Input: Cricket is my favourite sport +++ Input 23/
           05/23: Problem Summary This is test scenario +++ Input: I love travelling  


That said, you can simplify this trick greatly which may solve the issue:

select keyvalue,
  xmlagg (
    xmlelement ( 
      e, text, ' ' 
    ).extract ('//text()') 
    order by counter
  ).getclobval () csv
from   test1
group by keyvalue;

  KEYVALUE CSV                                                                             
---------- --------------------------------------------------------------------------------
      2001 +++ Input: My Name is ABC +++ Input: Cricket is my favourite sport +++ Input 23/
           05/23: Problem Summary This is test scenario +++ Input: I love travelling 


If you want to remove "Problem Summary" lines from the results, but preserve the starting +++:

- Have a CASE expression that identifies rows with values you want to remove
- Use substr or similar to remove everything after the delimiter
- Otherwise return the full text

e.g.:

select keyvalue,
  xmlagg (
    xmlelement ( 
      e, 
      case
        when text like '%Problem Summary%'
        then substr ( text, 1, 3 )
        else text
      end, ' ' 
    ).extract ('//text()') 
    order by counter
  ).getclobval () csv
from   test1
group by keyvalue;

  KEYVALUE CSV                                                                             
---------- --------------------------------------------------------------------------------
      2001 +++ Input: My Name is ABC +++ Input: Cricket is my favourite sport +++ +++ Input
           : I love travelling   

Rating

  (2 ratings)

Is this answer out of date? If it is, please let us know via a Comment

Comments

Deprecated XML functions

mathguy, March 29, 2023 - 2:18 pm UTC

The OP's version is 20.2. XML functions extract and getclobval are deprecated as of 11.2. The "modern" way to do the same is something like

........
xmlcast(xmlagg(xmlelement(t, text) order by counter) as clob)
........

Chris Saxon
March 29, 2023 - 5:29 pm UTC

Good point on the deprecated functions.

Though there is no version 20.2 of Oracle Database; I suspect that's their APEX version...

More to Explore

SQL

The Oracle documentation contains a complete SQL reference.