I have a following xml in CLOB column of a table. I want to retrieve this column via SQL. I tried the following query but it failed. Can someone help me in this regard Thanks.
XML
<exceptionInformation/>
<retryDetails>
<numberOfRetries>0</numberOfRetries>
<isToDoEntrySuppressed>false</isToDoEntrySuppressed>
</retryDetails>
<initiatedByGis>W1FL</initiatedByGis>
SQL I am Trying
SELECT TRIM(XMLTYPE(bo_data_area).extract('/retryDetails/numberOfRetries/text()').getStringVal()) bo_data_area from W1_ACTIVITY WHERE act_id = 91047445388697;
Error
ORA-31011: XML parsing failed
ORA-19213: error occurred in XML processing at lines 1
LPX-00245: extra data after end of document
ORA-06512: at "SYS.XMLTYPE", line 272
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.