Hello guys,
I have a weird problem. Imagine the following scenario...
CREATE TABLE my_body_test
(
id number(10),
rnk NUMBER(10),
body VARCHAR2(1000)
);
INSERT INTO my_body_test VALUES(1, 1, 'Hello ');
INSERT INTO my_body_test VALUES(1, 2, 'World [');
INSERT INTO my_body_test VALUES(1, 2, CHR(12));
INSERT INTO my_body_test VALUES(1, 2, ']!!');
DROP TABLE my_body_test;
I would like to aggregate the content of the column body. I can't une SYS_CONNECT_BY_PATH because the body contains a lot of data and can be more than 4000 chars. So, I do it this way:
SELECT id,
dbms_xmlgen.convert(xmlagg(xmlelement(e, body) ORDER BY rnk ).extract('//text()').getClobVal(), 1)
FROM my_body_test
GROUP BY id;
But I have the following error.
ORA-31011: XML parsing failed
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.
I know it's because of the third row inserted [CHR(12)]. But is there any way to fix this?? I'm using 10g (LISTAGG is not an option).
Thanks for your advice,