Hello I am using following regexp_substr
TO_CHAR(SUBSTR(
REGEXP_REPLACE(response, '^.*<TEXT>(.*)</TEXT>.*$', '\1', 1, 0, 'mn') ,1,200) ) , but there are multiple TEXT TAGS, how can I combine.
=========
<?xml version='1.0' encoding='UTF-8'?><err><sqlerrm><![CDATA[ORA-31011: XML parsing failed]]></sqlerrm><response><![CDATA[------=_Part_1609_840331386.1705798239470
Content-Type: application/xop+xml;charset=UTF-8;type="text/xml"
Content-Transfer-Encoding: 8bit
Content-ID: <c17b5d63-d1e9-436d-9590-0cd78c3c8021>
<env:Envelope xmlns:env="http://schemas.xmlsoap.org/soap/envelope/"><env:Header/><env:Body><env:Fault><faultcode>env:Server</faultcode><faultstring>JBO-FND:::FND_GENERIC_MULTI_MESSAGE: <MESSAGE><NUMBER></NUMBER><TEXT>Multiple errors occurred while processing this request. See Details.</TEXT><CAUSE></CAUSE><ACTION></ACTION><DETAILS></DETAILS><INCIDENT></INCIDENT></MESSAGE>JBO-AR:::AR_RAPI_TRX_ID_INST_INVALID: <MESSAGE><NUMBER>AR-855792</NUMBER><TEXT>You must enter a valid combination of customer transaction ID and installment.</TEXT><CAUSE></CAUSE><ACTION></ACTION><DETAILS></DETAILS><INCIDENT></INCIDENT></MESSAGE>JBO-AR:::GENERIC_MESSAGE: <MESSAGE><NUMBER>AR-855636</NUMBER><TEXT>APPLY : ORA-01403: no data found</TEXT><CAUSE></CAUSE><ACTION></ACTION><DETAILS></DETAILS><INCIDENT></INCIDENT></MESSAGE>JBO-FND:::FND_AS_UNEXPECTED_ERROR: <MESSAGE><NUMBER>FND-3323</NUMBER><TEXT>An unexpected error has occurred.</TEXT><CAUSE></CAUSE><ACTION></ACTION><DETAILS>In package AR_RECEIPT_API_PUB and procedure Apply the following error occurred: ORA-01403: no data found.</DETAILS><INCIDENT></INCIDENT></MESSAGE></faultstring><detail><tns:ServiceErrorMessage xmlns:tns="http://xmlns.oracle.com/adf/svc/errors/" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"><tns:code>FND:::FND_GENERIC_MULTI_MESSAGE</tns:code><tns:message>JBO-FND:::FND_GENERIC_MULTI_MESSAGE: <MESSAGE><NUMBER></NUMBER><TEXT>Multiple errors occurred while processing this request. See Details.</TEXT><CAUSE></CAUSE><ACTION></ACTION><DETAILS></DETAILS><INCIDENT></INCIDENT></MESSAGE></tns:message><tns:severity>SEVERITY_ERROR</tns:severity><tns:detail xsi:type="tns:ServiceErrorMessage"><tns:code>AR:::AR_RAPI_TRX_ID_INST_INVALID</tns:code><tns:message>JBO-AR:::AR_RAPI_TRX_ID_INST_INVALID: <MESSAGE><NUMBER>AR-855792</NUMBER><TEXT>You must enter a valid combination of customer transaction ID and installment.</TEXT><CAUSE></CAUSE><ACTION></ACTION><DETAILS></DETAILS><INCIDENT></INCIDENT></MESSAGE></tns:message><tns:severity>SEVERITY_ERROR</tns:severity><tns:exceptionClassName>oracle.apps.fnd.applcore.messages.ApplcoreException</tns:exceptionClassName></tns:detail><tns:detail xsi:type="tns:ServiceErrorMessage"><tns:code>AR:::GENERIC_MESSAGE</tns:code><tns:message>JBO-AR:::GENERIC_MESSAGE: <MESSAGE><NUMBER>AR-855636</NUMBER><TEXT>APPLY : ORA-01403: no data found</TEXT><CAUSE></CAUSE><ACTION></ACTION><DETAILS></DETAILS><INCIDENT></INCIDENT></MESSAGE></tns:message><tns:severity>SEVERITY_ERROR</tns:severity><tns:exceptionClassName>oracle.apps.fnd.applcore.messages.ApplcoreException</tns:exceptionClassName></tns:detail><tns:detail xsi:type="tns:ServiceErrorMessage"><tns:code>FND:::FND_AS_UNEXPECTED_ERROR</tns:code><tns:message>JBO-FND:::FND_AS_UNEXPECTED_ERROR: <MESSAGE><NUMBER>FND-3323</NUMBER><TEXT>An unexpected error has occurred.</TEXT><CAUSE></CAUSE><ACTION></ACTION><DETAILS>In package AR_RECEIPT_API_PUB and procedure Apply the following error occurred: ORA-01403: no data found.</DETAILS><INCIDENT></INCIDENT></MESSAGE></tns:message><tns:severity>SEVERITY_ERROR</tns:severity><tns:exceptionClassName>oracle.apps.fnd.applcore.messages.ApplcoreException</tns:exceptionClassName></tns:detail><tns:exceptionClassName>oracle.apps.fnd.applcore.messages.ApplcoreException</tns:exceptionClassName></tns:ServiceErrorMessage></detail></env:Fault></env:Body></env:Envelope>
------=_Part_1609_840331386.1705798239470--
]]></response></err>