Skip to Main Content
Forums

DevOps, CI/CD and Automation

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!

Count of a node in xml

User_KEHP1Jun 2 2020 — edited Jun 2 2020

Hi

How can I get the count of a particular node in the xmltype data. In the below xmltype data, need to check the count of 'actionOf' node i.e how many time it is appearing the xml.

<ExecutionReport xmlns="http://www.fpml.org/FpML-5/recordkeeping" xmlns:fpml="http://www.fpml.org/FpML-5/recordkeeping" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" fpmlVersion="5-5">

<header>

<creationTimestamp>2020-05-28T13:39:02Z</creationTimestamp>

</header>

<isCorrection>false</isCorrection>

<actionOf>

<Reference href="partyA"/>

</actionOf>

<actionOf>

<Reference href="partyB"/>

</actionOf>

</ExecutionReport>

-- Version

Oracle Database 12c Enterprise Edition Release 12.1.0.2.0 - 64bit Production

PL/SQL Release 12.1.0.2.0 - Production

"CORE 12.1.0.2.0 Production"

TNS for Linux: Version 12.1.0.2.0 - Production

NLSRTL Version 12.1.0.2.0 - Production

Regards
This post has been answered by mNem on Jun 2 2020
Jump to Answer

Comments

mNem
Answer 
select * from  xmltable (
  xmlnamespaces (default 'http://www.fpml.org/FpML-5/recordkeeping')
  ,
  '/ExecutionReport/count(actionOf)'
  passing 
  xmltype('<ExecutionReport xmlns="http://www.fpml.org/FpML-5/recordkeeping" xmlns:fpml="http://www.fpml.org/FpML-5/recordkeeping" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" fpmlVersion="5-5">
  <header>
  <creationTimestamp>2020-05-28T13:39:02Z</creationTimestamp>
  </header>
  <isCorrection>false</isCorrection>
  <actionOf>
  <Reference href="partyA"/>
  </actionOf>
  <actionOf>
  <Reference href="partyB"/>
  </actionOf>
  </ExecutionReport>')
  columns 
  cnt number path '.'
);
       CNT
----------
         2
Marked as Answer by User_KEHP1 · Jun 2 2020
2 people found this helpful
A.JOSH

Thanks, this helps.

1 - 2

Post Details

Added on Jun 2 2020
#pl-sql-xml-programming, #sql-pl-sql, #xml
2 comments
1,405 views