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.

  • simple SUBSTR and INSTR should suffice. And why are you storing `xmltype` into `CLOB`? – Lalit Kumar B Feb 02 '15 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. –  Feb 02 '15 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. – Lalit Kumar B Feb 02 '15 at 11:58

3 Answers3

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

Tamim Al Manaseer
  • 3,554
  • 3
  • 24
  • 33
  • Yes its didigts only. Can you please tell me how to query it . –  Feb 02 '15 at 11:10
  • @ajay updated the answer to show an example and a link to a working query on sqlfiddle – Tamim Al Manaseer Feb 02 '15 at 11:26
  • I tried this query but in query result its now showing anything. Its returning null :( –  Feb 02 '15 at 11:27
  • its working fine on the sql fiddler link I shared, make sure your data has the tag '' – Tamim Al Manaseer Feb 02 '15 at 11:30
  • 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 '' –  Feb 02 '15 at 11:31
  • @ajay ya it works with clob too, here is a modified sql fiddler http://sqlfiddle.com/#!4/9f383/1, check it out, see whats the difference with your case – Tamim Al Manaseer Feb 02 '15 at 11:36
  • Regular expression would work with clob type but would be lot slower than dbms_lob.substr and dbms_lob.instr – psaraj12 Feb 02 '15 at 11:37
  • It still showing null. I think i have too many in this string. Thats why its the problem. I confirmed now about this. So i found a unique string to get the ID. If we want to use like to get the ID then how can we do that ? 201501111956384981 –  Feb 02 '15 at 11:49
  • @psaraj12, From 10g on wards, you don't need `DBMS_LOB`, simple `SUBSTR` would work fine. – Lalit Kumar B Feb 02 '15 at 11:54
  • You can see the updated question now. We have too many string like . So i think we have to take more string in order to make it unique and identify the ID in between that. The string is unique. –  Feb 02 '15 at 12:04
  • using substr and instr would be lot slower than using dbms_lob.substr and dbms_lob.instr hence didn't mention – psaraj12 Feb 02 '15 at 12:04
  • @TamimSalem i have changed my question now with the query and data. Please check it –  Feb 02 '15 at 12:14
  • @ajay REGEXT_SUBSTR doesn't care if you have multiple matches, it will return the first by default, not null. I'm afraid I can't help you further, you have the Oracle documentation and some sample code, you can pursue the answer on your own now. – Tamim Al Manaseer Feb 02 '15 at 12:19
  • @psaraj, *a lot slower* ??? You mean the latest releases claiming the use of SUBSTR directly instead of DBMS_LOB.SUBSTR has got performance issues? Citation needed pal. – Lalit Kumar B Feb 02 '15 at 14:27
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
evilive
  • 916
  • 1
  • 7
  • 24
  • Its given the same error i.e inconsistent datatype . –  Feb 02 '15 at 11:22
  • Because you have missed to see that the answer is for xmltype and not for CLOB data type. – Lalit Kumar B Feb 02 '15 at 11:57
  • @ajay - which goes back to [your earlier question](http://stackoverflow.com/q/28214725/266304). You can convert it, but then you just end up back with your database link problem. – Alex Poole Feb 02 '15 at 12:16
  • Yes thats why i am trying with the SUBSTR function in order the problem of database link –  Feb 02 '15 at 12:42
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;
psaraj12
  • 4,772
  • 2
  • 21
  • 30
  • You don't need `DBMS_LOB.SUBSTR` in newer versions, at least from 10g onwards. – Lalit Kumar B Feb 23 '15 at 07:53
  • As per my testing Substr is slower in 11g release1 http://stackoverflow.com/questions/27547107/in-oracle-is-it-possible-to-convert-very-large-string-clob-separated-by-comma/27548525#27548525 – psaraj12 Feb 23 '15 at 09:38
  • 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`. – Lalit Kumar B Feb 23 '15 at 09:50