-- 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.
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