I've been scouring posts for the last several days and have yet to find the solution that will work for me. I can extract XML values with CLOB's that do not contain name spaces, but I'm struggling with the latter.
I'm attempting to extract employeeId, tns:ticketID and tns:status. This is what I've got so far, but the ticketId and ticketStatus both return blank. And I'm not quite certain how I can work in the employeeId with this solution either. Any help would be appreciated.
SELECT st.*
, extractvalue(value(p), '//employeeId') as employeeId
, extractvalue(value(p), '//Status//ticketID') as ticketId
, extractvalue(value(p), '//Status//status') as ticketStatus
FROM soadapter2.transaction st
, Table(XMLSequence(extract(xmltype(st.input), '//tns:Status', 'xmlns:tns="http://www.antennasoftware.com/TKSmart/"'))) p
where st.pk=55055056
<?xml version="1.0" encoding="UTF-8"?>
<StatusUpdate xmlns="http://www.antennasoftware.com/TKSmart/" xmlns:xsd="http://www.w3.org/2001/XMLSchema" xmlns:tns="http://www.antennasoftware.com/TKSmart/">
<employeeId>1234</employeeId>
<tns:Status>
<tns:ticketID>123456</tns:ticketID>
<tns:status>accepted</tns:status>
<tns:ticketType>SAFE</tns:ticketType>
<tns:actualStartTime></tns:actualStartTime>
<tns:actualEndTime></tns:actualEndTime>
<tns:actualDuration></tns:actualDuration>
<tns:incompleteCode></tns:incompleteCode>
<tns:completedUnits></tns:completedUnits>
<tns:changedUnits></tns:changedUnits>
<tns:etaDescription>Auto-Accepted</tns:etaDescription>
<tns:etaDateTime>2016-05-31T15:26:14Z</tns:etaDateTime>
<tns:rejectionCodeId></tns:rejectionCodeId>
</tns:Status>
</StatusUpdate>
For reference, I am doing this successfully, but the XML has no namespaces in them.
SELECT saf.*,
extractvalue(value(p), '/Employee/@rowstate') as ticketRowState,
extractvalue(value(p), 'Employee/ticketID') as ticketId
FROM imp_dex_outbound_msg saf,
Table(XMLSequence(extract(xmltype(saf.message), '/TicketData/Employee'))) p