Oracle 21c

By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.
create table PDI_SUBMITTED_XML
(PSX_AGREEMENT  NUMBER(10),
 PSX_DOCUMENT   CLOB);
insert into PDI_SUBMITTED_XML (PSX_AGREEMENT, PSX_DOCUMENT)
values (42, to_clob('<?xml version="1.0" encoding="UTF-8"?>
<tns:AgreementWrapper xmlns:tns="http://ws.pancredit.com/wsdl" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance">
  <tns:Agreement>
    <tns:AdminFee>199</tns:AdminFee>
    <tns:AdminFeeFinanced>true</tns:AdminFeeFinanced>
    <tns:Affordability>
      <tns:DownturnReason/>
    </tns:Affordability>
    <tns:AgreementNumber>13498443</tns:AgreementNumber>
    <tns:BankAccountAlreadyValidated>false</tns:BankAccountAlreadyValidated>
    <tns:BankAccountNumber>70872490</tns:BankAccountNumber>
    <tns:BankSortCode>404784</tns:BankSortCode>
    <tns:BranchName>Broker Region</tns:BranchName>
    <tns:BrandName>Rtl - VAT Assist Brand</tns:BrandName>
  </tns:Agreement>
</tns:AgreementWrapper>'));
1 rows affected

select xmlquery(
  'declare namespace tns="http://ws.pancredit.com/wsdl";
  /tns:AgreementWrapper/tns:Agreement/tns:BranchName/text()'
  passing xmltype(PSX_DOCUMENT)
  returning content
  ).getstringval() as branch_name
from PDI_SUBMITTED_XML


BRANCH_NAME


Broker Region


select x.agreement_number, x.branch_name
from PDI_SUBMITTED_XML
cross apply xmltable(
  xmlnamespaces('http://ws.pancredit.com/wsdl' as "tns"),
  '/tns:AgreementWrapper/tns:Agreement'
  passing xmltype(PSX_DOCUMENT)
  columns
    agreement_number number path 'tns:AgreementNumber',
    branch_name varchar2(30) path 'tns:BranchName'
) x


AGREEMENT_NUMBER
BRANCH_NAME


13498443
Broker Region