0

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.

Improve this question
3
  • simple SUBSTR and INSTR should suffice. And why are you storing xmltype into CLOB? Commented Feb 2, 2015 at 11:15
  • I did not use it. Its from the client side database where i am querying. I dont have write permission to change the column type. Only i can use Select statement in this database and query for table. Commented Feb 2, 2015 at 11:26
  • You said that the original data is not the same as you have provided in the question. Why don't you edit your question and provide correct information to avoid unnecessary discussion. Commented Feb 2, 2015 at 11:58

3 Answers 3

Reset to default
2

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

Improve this answer
Sign up to request clarification or add additional context in comments.

14 Comments

Yes its didigts only. Can you please tell me how to query it .
@ajay updated the answer to show an example and a link to a working query on sqlfiddle
I tried this query but in query result its now showing anything. Its returning null :(
its working fine on the sql fiddler link I shared, make sure your data has the tag '<ax2130:id>'
I mentioned in code that RESPONSE_XML is of CLOB type. Will it work with CLOB datatype ? And i just copy paste your query. So it contains '<ax2130:id>'
|
0

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
Improve this answer

4 Comments

Its given the same error i.e inconsistent datatype .
Because you have missed to see that the answer is for xmltype and not for CLOB data type.
@ajay - which goes back to your earlier question. You can convert it, but then you just end up back with your database link problem.
Yes thats why i am trying with the SUBSTR function in order the problem of database link
0

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;
Improve this answer

3 Comments

You don't need DBMS_LOB.SUBSTR in newer versions, at least from 10g onwards.
As per my testing Substr is slower in 11g release1 stackoverflow.com/questions/27547107/…
Slower compared to what? Your link is comparing it with regexp, in that case substr will be always faster than regexp. By the way, my point is not about regexp. I said, you could simply use SUBTR instead of DBMS_LOB.SUBSTR.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.