I have the table which has clob datatype for one column(Attributes), but it stores xml data. How to extract this xml data into different columns? Any suggestions
<Attributes>
<Map>
<entry key="approvalMode" value="serial"/>
<entry key="approvalScheme" value="owner, identity"/>
<entry key="approvalSet">
<value>
<ApprovalSet>
<ApprovalItem application="IIQ" approver="REEDC2" assignmentId="9e8d51b8f27446618f829e775c3661f1" displayName="Role" displayValue="Cyberlife - CLBCALL1 (Production)" id="bb59896ef167450a955fb8576341005d" name="assignedRoles" operation="Add" state="Finished" value="Cyberlife - CLBCALL1">
<Attributes>
<Map>
<entry key="flow" value="AccessRequest"/>
<entry key="id" value="8adc34b36d2d23ea016d2d6cf274096d"/>
<entry key="interface" value="LCM"/>
<entry key="operation" value="RoleAdd"/>
</Map>
</Attributes>
</ApprovalItem>
</ApprovalSet>
</value>
</entry>
<entry key="approvalSplitPoint" value="owner"/>
<entry key="clearApprovalDecisions">
<value>
<Boolean>true</Boolean>
</value>
</entry>
<entry key="flow" value="AccessRequest"/>
<entry key="identityDisplayName" value="Liberatore, Ronald Scott"/>
<entry key="identityName" value="LIBER37"/>
<entry key="identityRequestId" value="0000243461"/>
<entry key="launcher" value="HANNEME"/>
<entry key="policyViolations"/>
<entry key="workItemDescription"/>
<entry key="workItemEscalationFrequency"/>
<entry key="workItemEscalationRule"/>
<entry key="workItemEscalationTemplate"/>
<entry key="workItemHoursTillExpiration" value="336"/>
<entry key="workItemPriority">
<value>
<WorkItemLevel>Normal</WorkItemLevel>
</value>
</entry>
</Map>
</Attributes>
Here is the query.
select to_date('19700101', 'YYYYMMDD') + ( 1 / 24 / 60 / 60 /1000) * identityiq.spt_work_item_archive.created as created_date,
to_date('19700101', 'YYYYMMDD') + ( 1 / 24 / 60 / 60 /1000) * identityiq.spt_work_item_archive.archived as archived_date,
IDENTITYIQ.SPT_WORK_ITEM_ARCHIVE.name,identityiq.spt_work_item_archive.type,
identityiq.spt_work_item_archive.attributes,
identityiq.spt_work_item_archive.completer,
identityiq.spt_work_item_archive.requester
from IDENTITYIQ.SPT_WORK_ITEM_ARCHIVE where identityiq.spt_work_item_archive.type in ('ManualAction','Remediation');