Skip to Main Content

SQL & PL/SQL

Announcement

For appeals, questions and feedback about Oracle Forums, please email oracle-forums-moderators_us@oracle.com. Technical questions should be asked in the appropriate category. Thank you!

String Aggregation Using XML

user13117585Feb 7 2013 — edited Feb 8 2013
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,
Comments
Locked Post
New comments cannot be posted to this locked post.
Post Details
Locked on Mar 8 2013
Added on Feb 7 2013
10 comments
1,182 views