By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.
create table traptabclob(testclob clob);
insert into traptabclob values('<?xml version="1.0" encoding="UTF-8"?>
<tns:InputOutputScoringDetails xmlns:tns="jdhajdjh" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xsi:schemaLocation="dasd.xsd ">
<tns:InputsOutputs>
<tns:NameValue>
<tns:Name>ABSC_APPLICANTBUREAUSCORE</tns:Name>
<tns:Value>0.11030000000000001</tns:Value>
</tns:NameValue>
<tns:NameValue>
<tns:Name>ABS_BN_SCORE_DETAIL_PK</tns:Name>
<tns:Value>10035</tns:Value>
</tns:NameValue>
</tns:InputsOutputs>
</tns:InputOutputScoringDetails>
');
1 rows affected
SELECT EXTRACTVALUE(xmltype(testclob), '/tns:InputOutputScoringDetails/tns:InputsOutputs/tns:NameValue[1]/tns:Name')
FROM traptabclob;
ORA-31011: XML parsing failed
ORA-19202: Error occurred in XML processing
LPX-00601: Invalid token in: '/tns:InputOutputScoringDetails/tns:InputsOutputs/tns:NameValue[1]/tns:Name'
SELECT EXTRACTVALUE(
xmltype(testclob),
'/tns:InputOutputScoringDetails/tns:InputsOutputs/tns:NameValue[1]/tns:Name',
'xmlns:tns="jdhajdjh"'
) AS result
FROM traptabclob;
| RESULT |
|---|
| ABSC_APPLICANTBUREAUSCORE |
SELECT XMLQuery(
'declare namespace tns="jdhajdjh";
/tns:InputOutputScoringDetails/tns:InputsOutputs/tns:NameValue[1]/tns:Name/text()'
PASSING xmltype(testclob)
RETURNING CONTENT
) AS result
FROM traptabclob;
| RESULT |
|---|
| ABSC_APPLICANTBUREAUSCORE |
SELECT x.name, x.value
FROM traptabclob t
CROSS APPLY XMLTable(
XMLNamespaces ('jdhajdjh' AS "tns"),
'/tns:InputOutputScoringDetails/tns:InputsOutputs/tns:NameValue'
PASSING xmltype(testclob)
COLUMNS
name VARCHAR2(30) PATH 'tns:Name',
value NUMBER PATH 'tns:Value'
) x;
| NAME | VALUE |
|---|---|
| ABSC_APPLICANTBUREAUSCORE | .11030000000000001 |
| ABS_BN_SCORE_DETAIL_PK | 10035 |