Getting XML values from a Oracle CLOB database column

Question

By using either PL/SQL or SQL I'm trying to extract specific XML values from a database CLOB column.

Table : PDI_SUBMITTED_XML
(PSX_AGREEMENT  NUMBER(10),
 PSX_DOCUMENT   CLOB)

For example I'm trying to extract the value "Broker Region" from the BranchName tag below from the actual CLOB contents.

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

Up to now I've found no solution that will do this, someone has mentioned XMLPATH but see not many examples on internet that does it for the above example.

Does anybody have a solution to what seems simple enough to do but I've had no success up to now.

Alex Poole · Accepted Answer · 2022-07-21 13:40:05Z

1

You can use XMLQuery to get a single value:

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

Or XMLTable if you need to get several things at once:

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

In both cases the tns namespace has to be declared (unless you wildcard it).

db<>fiddle

Read more about those functions in the documentation.

answered Jul 21, 2022 at 13:40

Alex Poole

192k11 gold badges198 silver badges349 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

Shaun Kinnair Over a year ago

Wow... @Alex Poole you are a star.... quick response :-)

psaraj12 · Accepted Answer · 2022-07-21 14:25:41Z

0

Adding to Alex Poole's answer one more alternative.The DB fiddle here

    SELECT 
extractvalue(xmltype(PSX_DOCUMENT),'/AgreementWrapper/Agreement/BranchName',
    'xmlns="http://ws.pancredit.com/wsdl"') as Branch_name
    from PDI_SUBMITTED_XML;

For multiple values, you can use multiple extractvalues.The DB Fiddle here

    SELECT 
      extractvalue(
        xmltype(PSX_DOCUMENT), 
        '/AgreementWrapper/Agreement/BranchName', 
        'xmlns="http://ws.pancredit.com/wsdl"'
      ) as BRanch_name, 
      extractvalue(
        xmltype(PSX_DOCUMENT), 
        '/AgreementWrapper/Agreement/AgreementNumber', 
        'xmlns="http://ws.pancredit.com/wsdl"'
      ) as AgreementNumber 
    from 
      PDI_SUBMITTED_XML;

edited Jul 21, 2022 at 14:25

answered Jul 21, 2022 at 14:19

psaraj12

5,0783 gold badges25 silver badges34 bronze badges

Collectives™ on Stack Overflow

Getting XML values from a Oracle CLOB database column

2 Answers 2

1 Comment

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

1 Comment

Comments

Your Answer

Sign up or log in

Post as a guest

Related