CODEWITHSUNDEEP
sqlstringoracle-databasesubstringclob
user1930857
user1930857

Reputation:

How to get the value between 2 string in oracle sql

enter image description hereI have a SOAP_MONITORING table which has RESPONSE_XML column which is CLOB datatype and consist of large string. I have requirement to fetch and show all the SUBSCRIPTION_ID which is hidden in this string. The SUBSCRIPTION_ID resides in this string : <ax2147:subscriptions xsi:type="ax2127:SubscriptionDTO"><ax2130:id>201411211617575057</ax2130:id> . I have to get all ID which is nothing but my SUBSCRIPTION_ID which resides in between <ax2147:subscriptions xsi:type="ax2127:SubscriptionDTO"><ax2130:id> and </ax2130:id> string. I tried the below query :

   SELECT REPLACE(REPLACE(MatchedId, '<ax2147:subscriptions xsi:type="ax2127:SubscriptionDTO"><ax2130:id>', ''), '</ax2130:id>', '') 
FROM
(
SELECT   REGEXP_SUBSTR(RESPONSE_XML, '<ax2147:subscriptions xsi:type="ax2127:SubscriptionDTO"><ax2130:id>\d+</ax2130:id>') 
FROM  SOAP_MONITORING 
)
WHERE 
WEB_SERVICE_NAME='RatorWebShopService' and WEB_METHOD_NAME='placeShopOrder'

But received an empty result.

Upvotes: 0

Views: 3446

Answers (3)

psaraj12
psaraj12

Reputation: 5072

Kindly check the below for your edited question

 with unique_data as (select '<ax2147:subscriptions xsi:type="ax2127:SubscriptionDTO">' data from dual)

    select  SUBSTR(MatchedId,instr(MatchedId,'>')+1 , instr(MatchedId,'</') - length('</ax2130:id>')) from
    (
    SELECT   replace(dbms_lob.substr(RESPONSE_XML,length(response_xml),dbms_lob.instr(response_xml,data)),data,'') AS MatchedId
    FROM     SOAP_MONITORING,unique_data
    )
    WHERE 
    MatchedId is not null;

Upvotes: 0

Tamim Al Manaseer
Tamim Al Manaseer

Reputation: 3724

I think this is a case where regular expressions can help you. here is a reference from oracle documentation.

the regex expression could be something like:

<ax2130:id>\d+</ax2130:id>

if your IDs are digits only.

UPDATE

Here is also a sample query you can use:

SELECT REPLACE(REPLACE(MatchedId, '<ax2130:id>', ''), '</ax2130:id>', '') AS CleanMatch
FROM
(
    SELECT   REGEXP_SUBSTR(RESPONSE_XML, '<ax2130:id>\d+</ax2130:id>') AS MatchedId
    FROM     SOAP_MONITORING
)
WHERE 
MatchedId is not null

http://sqlfiddle.com/#!4/d473c/5

Upvotes: 2

evilive
evilive

Reputation: 996

CLOB can't be handled like varchar in many ways If your column contains XML you should consider using Oracles xmltype datatype. In that case you coud use XML-Tools to query your data: i.e.

select extractvalue(response_xml, '/response_xml/id/text()')
from soap_monitoring
order by  extractvalue(response_xml, '/response_xml/id/text()') desc

Upvotes: 0

Related Questions