1. How to extract a particular xml tag value from a column with CLOB datatype.
Example:
<REQUEST_DETAIL>
<GROUP_TYPE>PR</GROUP_TYPE>
<GROUP_NAME>DATA ENGINEERING ASSET MANAGEMENT</GROUP_NAME>
<BUS_UNIT_ACRN>SCS-FCAT</BUS_UNIT_ACRN>
<PROJ_MGR_ID></PROJ_MGR_ID>
<PROJ_MGR_NAME>Roland Roy</PROJ_MGR_NAME>
</REQUEST_DETAIL>
select
xmltype(e.x).extract('/REQUEST_DETAIL/GROUP_TYPE/text()').getStringVal()
from t e
/
above example works.
If I dont know the order of xml tag to be queried how can I get the value.
(*****/****/***/***/REQUEST_DETAIL/GROUP_TYPE/***/**** )
2.using DBMS_LOB.SUBSTR is taking more time to execute and it never return results and session goes wait status for accesing LOB_INDEX (concurrently the CLOB column is used by application).
Is there any other option to get the results quicker.
SELECT DBMS_LOB.SUBSTR (xml_blob, 4, DBMS_LOB.INSTR (xml_blob, '<Name>', 1, 1 ) + 6 ) name,
status,
COUNT (*) cnt
FROM LOG_TBL
WHERE TRUNC(IO_DATE)=TRUNC(SYSDATE)
GROUP BY DBMS_LOB.SUBSTR (xml_blob, 4, DBMS_LOB.INSTR (xml_blob, '<Name>', 1, 1 ) + 6 ),
status
Regards,
Veera