SQL & PL/SQL

Announcement

For appeals, questions and feedback about Oracle Forums, please email oracle-forums-moderators_us@oracle.com. Technical questions should be asked in the appropriate category. Thank you!

query xml document in clob field to fetch required elements with namespaces

Ramakrishna BoppanaOct 10 2023 — edited Oct 10 2023

Hi ,

Please can you help me with select XML query to fetch below columns from clob column with the following XML document

senderid,messageid,timestamp ,result,description,errorNumber,ErrorDescription

XML document stored clob column:-

<?xml version="1.0" encoding="UTF-8" standalone="yes"?>
<ns4:IndividualResponse xmlns="http://www.xxxx.co.xxx/gts/shared/enterpriseMessage/v2"
xmlns:ns5="http://www.xxx.co.xxx/gts/custgtwy/CustMaintenanceService/Juristic/v3"
xmlns:ns2="http://www.xxx.co.xxx/gts/cdm/custgtwy/customerMaintenance/v3"
xmlns:ns4="http://www.xxx.co.xxx/gts/custgtwy/CustMaintenanceService/Individual/v3"
xmlns:ns3="http://www.xxx.co.xx/gts/cdm/custgtwy/v1">
<header>
<senderID>dre</senderID>
<messageID>dre mess-Indiv</messageID>
<timestamp>2023-10-06T17:42:15.389+02:00</timestamp>
<result>
<status>FAILURE</status>
<description>Check Error List for details</description>
</result>
</header>
<ns4:Response>
<ns2:ErrorList>
<ns3:errorNumber>505</ns3:errorNumber>
<ns3:ErrorDescription>50065-50999-NO ACESSS TO UPDATE BRANCH</ns3:ErrorDescription>
</ns2:ErrorList>
</ns4:Response>
</ns4:IndividualResponse>

Added on Oct 10 2023

#sql, #xml

4 comments

485 views