Hello,
I'm trying to read an xml file (log.xml on 11.2.0.4) using the following lines :
declare
c clob;
b bfile := bfilename('ALERT1','log.xml');
x NUMBER := 0;
p_src_offset NUMBER :=1;
p_dest_offset NUMBER :=1;
p_lang_context NUMBER:=DBMS_LOB.DEFAULT_LANG_CTX;
p_Warning NUMBER :=0;
begin
dbms_lob.open(b,dbms_lob.file_readonly);
dbms_lob.createtemporary(c,true);
dbms_lob.loadClobFromFile(dest_lob => c,src_bfile => b,amount => dbms_lob.getlength(b),dest_offset => p_dest_offset,src_offset => p_src_offset,bfile_csid => DBMS_LOB.DEFAULT_CSID,lang_context => p_lang_context,warning => p_Warning);
dbms_lob.close(b);
c:='<root>'||c||'</root>';
FOR cur_rec IN (
select tt.*
from XMLTABLE( '//msg[@comp_id="rdbms"]'
passing XMLTYPE(c)
columns "TEXT" VARCHAR2(300) path 'txt',
"TIME" VARCHAR2(19) PATH '@time',
"HOST_ID" VARCHAR2(14) PATH '@host_id',
"MODULE" VARCHAR2(14) PATH '@module'
) tt)
loop
.....
end loop;
dbms_lob.freetemporary(c);
end;
/
Problem is that is failing with :
ORA-31011: XML parsing failed
ORA-19202: Error occurred in XML processing
LPX-00243: element attribute value must be enclosed in quotes
After I checked the content of clob variable I found some "'" and """ combinations that were responsible for the error.
So, instead of using " passing XMLTYPE(c) " I switched to " passing XMLTYPE(replace(replace(c, CHR(38)||'apos;',''''),CHR(38)||'quot;','"')) and it fixed the problem.
However, I'm pretty sure that those are not the only special characters that may come from an xml and I'm not sure that this it's an optimal way to process this conversion, especially it there is a larger file.
Is there a way to process/replace those XML characters without using a replace function for every one of them ?
Thank you !