Regular expression showing wrong results in output in oracle sql

Question

I have to get the value between 2 strings. The SUBSCRIPTION_ID should always returns a digits. The ORDER_TYPE returns the alphabets,special characters. The ORDER_NUMBER always returns a digits. I am facing a problem to get the correct ORDER_TYPE. I dont know why but some of the value between 2 strings for ORDER_TYPE are showing null which is not to be null.The problem is only because of .+ , \d which is wrong and which i am trying to append from.Here is my complete xml string from which i am trying to extract the value between 2 strings:

<?xml version='1.0' encoding='utf-8'?><soapenv:Envelope xmlns:soapenv="http://schemas.xmlsoap.org/soap/envelope/"><soapenv:Body><ns:placeShopOrderResponse xmlns:ns="http://service.soap.CDRator.com"><ns:return xmlns:ax2133="http://signup.data.soap.CDRator.com/xsd" xmlns:ax2134="http://core.signup.data.soap.CDRator.com/xsd" xmlns:ax2127="http://data.soap.CDRator.com/xsd" xmlns:ax2129="http://webshop.data.soap.CDRator.com/xsd" xmlns:ax2130="http://core.data.soap.CDRator.com/xsd" xmlns:ax2140="http://core.result.service.soap.CDRator.com/xsd" xmlns:ax2139="http://result.service.soap.CDRator.com/xsd" xmlns:ax2147="http://webshop.result.service.soap.CDRator.com/xsd" xmlns:ax2148="http://mandate.result.service.soap.CDRator.com/xsd" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xsi:type="ax2147:PlaceShopOrderResultDTO"><ax2130:id xsi:nil="true" /><ax2140:code>0</ax2140:code><ax2130:description></ax2130:description><ax2130:stat>No Active BDM</ax2130:stat></ax2127:bdmStatus><ax2127:warningLogContent></ax2127:warningLogContent></ax2127:subscriptionCondition><ax2127:teleService xsi:type="ax2127:TeleServiceDTO"><ax2130:id>201501070917439768</ax2130:id><ax2130:code>TELE</ax2130:code><ax2127:serviceStatus xsi:type="ax2127:StatusDTO"><ax2130:id>100</ax2130:id><ax2130:description>Neu</ax2130:description><ax2130:stat>New</ax2130:stat></ax2127:serviceStatus><ax2130:imsi xsi:nil="true" /><ax2130:phoneNumber>NO_NUMBER</ax2130:phoneNumber><ax2127:imei xsi:nil="true" /><ax2127:simCard xsi:nil="true" /></ax2127:teleService></ax2147:subscriptions></ns:return></ns:placeShopOrderResponse></soapenv:Body></soapenv:Envelope>

Here is what i am trying:

SELECT TEMP_SOAP_MONITORING."ID", TO_NUMBER(REPLACE(REPLACE(REGEXP_SUBSTR(RESPONSE_XML,'<ax2147:subscriptions xsi:type="ax2127:SubscriptionDTO"><ax2130:id>\d+</ax2130:id>'),
'<ax2147:subscriptions xsi:type="ax2127:SubscriptionDTO"><ax2130:id>',''),'</ax2130:id>','')) 
   AS SUBSCRIPTION_ID , 
   CAST(REPLACE(REPLACE(
  REGEXP_SUBSTR(REQUEST_XML,'<ns7:orderType>.+</ns7:orderType>'),'<ns7:orderType>',''),'</ns7:orderType>','')
  AS VARCHAR(100)) AS ORDER_TYPE,
  TO_NUMBER(REPLACE(REPLACE(REGEXP_SUBSTR(RESPONSE_XML,'<ax2147:orderNumber>\d+</ax2147:orderNumber>'),'<ax2147:orderNumber>',''),'</ax2147:orderNumber>','')) 
   AS ORDER_NUMBER 
   FROM
   TEMP_SOAP_MONITORING;

The value for order_type will be something like NEW, SELF_REGISTRATION or it shouls be any special case which i want to get in results.I dont know but may be there are spaces thats why returning null. — Marcus
– Marcus, Commented Feb 11, 2015 at 15:09
it would be very helpful to see (a) the data you're trying to parse and (b) what you expect the parsed results to look like. — David Faber
– David Faber, Commented Feb 11, 2015 at 15:11
I'm sorry if I'm not making myself understood. You have a problem with ORDER_TYPE. What does ORDER_TYPE typically look like before you use a regular expression to try and extract a value from it? What is the value you're trying to extract? If you can answer those questions (and edit your question accordingly), then you make helping you much easier. — David Faber
– David Faber, Commented Feb 11, 2015 at 15:17

Marcus · Accepted Answer · 2015-02-12 10:33:42Z

I got the working query now. Please check this so that it can help others.

INSERT INTO TMP_SOAP_MONITORING_IDS ("ID",SUBSCRIPTION_ID,ORDER_TYPE,ORDER_NUMBER) 
SELECT ID,xt_req.SUBSCRIPTION_ID,xt_res.ORDER_TYPE,xt_rrr.ORDER_NUMBER
FROM TEMP_SOAP_MONITORING sm
CROSS JOIN XMLTable(XMLNAMESPACES (
      'http://schemas.xmlsoap.org/soap/envelope/' AS "soapenv",
      'http://service.soap.CDRator.com' as "ns",
      'http://core.data.soap.CDRator.com/xsd' as "ax2130",
      'http://webshop.result.service.soap.CDRator.com/xsd' as "ax2147"
    ),
    'for $i in /soapenv:Envelope/soapenv:Body/ns:placeShopOrderResponse/ns:return/ax2147:subscriptions
      return $i/ax2130:id'
    passing XMLType(sm.RESPONSE_XML)
    columns "SUBSCRIPTION_ID" number path '/') xt_req
CROSS JOIN XMLTable(XMLNAMESPACES (
       'http://webshop.data.soap.CDRator.com/xsd' as "ns7"
    ),
    'for $i in //ns7:orderType return $i'
    passing XMLType(sm.REQUEST_XML)
    columns "ORDER_TYPE" VARCHAR2(30) path '/') xt_res
CROSS JOIN XMLTable(XMLNAMESPACES (
      'http://webshop.result.service.soap.CDRator.com/xsd' as "ax2147"
    ),
    'for $i in //ax2147:orderNumber return $i'
    passing XMLType(sm.RESPONSE_XML)
    columns "ORDER_NUMBER" NUMBER path '/') xt_rrr;

COMMIT;


END EXT_SOAP_MONITORING;

Collectives™ on Stack Overflow

Regular expression showing wrong results in output in oracle sql

1 Answer 1

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related