Dear Forum members,
I'm getting the oracle xml parsing error if we receive the ampersand char or any special character in the xml tag value. if there is no special character the code is working fine.
i'm taking out the value coming within the xml quotes and printing it in text file using UTL_FILE utility. Coming ampersand is a possible scenarion in my case and i've to print the smae value in the file after extracting from the tag.
please advise me the best way to resolve this issue.
Oracle version used : 11.2.0.4.0
Working code:
SELECT x.*
FROM (select '<f4>Hello</f4><f50>SAM </f50>' line_notes from dual)
n,
XMLTABLE('/root'
passing xmltype('<root>'||line_notes||'</root>')
columns f4 VARCHAR2(17) PATH 'f4',
f50 VARCHAR2(65) PATH 'f50'
) X
Not working code:
SELECT x.*
FROM (select '<f4>Hello</f4><f50>SAM &</f50>' line_notes from dual)
n,
XMLTABLE('/root'
passing xmltype('<root>'||line_notes||'</root>')
columns f4 VARCHAR2(17) PATH 'f4',
f50 VARCHAR2(65) PATH 'f50'
) X
Error:
ORA-31011: XML parsing failed
ORA-19202: Error occurred in XML processing
LPX-00242: invalid use of ampersand ('&') character (use &)
Error at line 1
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.