SQL & PL/SQL

Hi .. I have a table called Item_details and have a column called work_item_data which has datatypes and the data inside the BLOB are always XML type and the size can be very huge like 4590264

There is a requirement to find a particular tag from the value and send it to the client .

Please provide me any suggestions or recommendations

Thanks in advance

The current version of Oracle is

Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - 64bit Production

PL/SQL Release 11.2.0.1.0 - Production

"CORE 11.2.0.1.0 Production"

TNS for Linux: Version 11.2.0.1.0 - Production

NLSRTL Version 11.2.0.1.0 - Production

I have attached one sample data for this

<Bancs>

   <action name="WFAddItem">

      <CASE_NBR>29670013</CASE_NBR>

      <BANK_NBR>0009</BANK_NBR>

      <REGION>002</REGION>

      <RAW_DATA_STRING>&lt;ATM_BLOCK_UNBLOCK&gt;&lt;CUSTOMER_NBR&gt;171&lt;/CUSTOMER_NBR&gt;&lt;ACCT_NBR&gt;300000007875&lt;/ACCT_NBR&gt;&lt;ACCT_TYPE&gt;&lt;/ACCT_TYPE&gt;&lt;CARD_NUMBER&gt;&lt;/CARD_NUMBER&gt;&lt;NAME_ON_CARD&gt;Mr Flash Gordon&lt;/NAME_ON_CARD&gt;&lt;CARD_TYPE&gt;&lt;/CARD_TYPE&gt;&lt;CARD_EXP_DATE&gt;&lt;/CARD_EXP_DATE&gt;&lt;CARD_STATUS&gt;&lt;/CARD_STATUS&gt;&lt;CARD_BLK_REASON&gt;&lt;/CARD_BLK_REASON&gt;&lt;FRAUD_BLOCK_EXP_DATE&gt;&lt;/FRAUD_BLOCK_EXP_DATE&gt;&lt;MAINTENANCE&gt;Block/Unblock&lt;/MAINTENANCE&gt;&lt;SEL_CARD_ID&gt;7287877129533840&lt;/SEL_CARD_ID&gt;&lt;SEL_CARD_TYPE&gt;&lt;/SEL_CARD_TYPE&gt;&lt;LINKED_CARD&gt;&lt;PARTY_NUM&gt;10&lt;/PARTY_NUM&gt;&lt;CARD_ID&gt;7287877129533840&lt;/CARD_ID&gt;&lt;CARD_NAME&gt;MR JOHN WATSON&lt;/CARD_NAME&gt;&lt;CARD_NUMBER&gt;6332207153510539&lt;/CARD_NUMBER&gt;&lt;CARD_TYPE&gt;SBK4&lt;/CARD_TYPE&gt;&lt;ACCOUNT_NUMBER&gt;126000-10000877&lt;/ACCOUNT_NUMBER&gt;&lt;CARD_STATUS&gt;Normal&lt;/CARD_STATUS&gt;&lt;EXPIRY_DATE&gt;2018-06-30&lt;/EXPIRY_DATE&gt;&lt;REF_TYPE&gt;ProgramID&lt;/REF_TYPE&gt;&lt;REF_IDENT&gt;SBPR4&lt;/REF_IDENT&gt;&lt;CARD_MAINT/&gt;&lt;/LINKED_CARD&gt;&lt;/ATM_BLOCK_UNBLOCK&gt;</RAW_DATA_STRING>

   </action>

</Bancs>

Name              Null     Type          

----------------- -------- --------------

WORK_ITEM_ID      NOT NULL NUMBER(31)    

FOLDER_ID         NOT NULL NUMBER(31)    

LOCKED_BY                  NUMBER(10)    

WORK_ITEM_TYPE    NOT NULL NVARCHAR2(32) 

WORK_ITEM_DATA             BLOB          

WORK_ITEM_FORMAT           NVARCHAR2(32) 

WORK_ITEM_COMMENT          NVARCHAR2(128)

WORK_ITEM_LINK             NVARCHAR2(1)  

WI_STATUS                  NVARCHAR2(1)  

CREATE_DATE                DATE          

UPDATE_DATE                DATE          

EXTRACTED_DATE             DATE          

ITEM_DATA_CLOB             CLOB