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;
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;