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!

How to remove duplicate values from XMLTYPE?

User_2K4XPMar 9 2022

I have the following XMLTYPE in which section between lines 7 to 20 is exactly same as section between lines 21 to 34.
How do I pick up only the distinct section.
Basically I need the TRIndicator and TRName, but on extraction it gives me a YESYES in TRIndicator and XYZXYZ in TRName.
Please advise
xml.pngWe are using Oracle Database 19c .
Thanks!

Comments

cormaco

Please post your example XML as actual text.

User_2K4XP

Hi,
Below is the xml as actual text.

<Party>
<ID>1234</ID>
<Type>Organisation</Type>
<Entity>ABCD</Entity>
<OrgType>Registered company</OrgType>
<PartyId>
<Design>7890</Design>
<Name>ACN</Name>
</PartyId>
<TRDetail>
<TRIndicator>yes</TRIndicator>
<TRName>XYZ</TRName>
</TRDetail>
</Party>
<Party>
<ID>1234</ID>
<Type>Organisation</Type>
<Entity>ABCD</Entity>
<OrgType>Registered company</OrgType>
<PartyId>
<Design>7890</Design>
<Name>ACN</Name>
</PartyId>
<TRDetail>
<TRIndicator>yes</TRIndicator>
<TRName>XYZ</TRName>
</TRDetail>
</Party>

Thanks !

cormaco

Here is one way:
It is necessary to use XMLPARSE because your example is an XML fragment, not a complete XML file.

with example(xmlfile) as (
select xmlparse(content
'<Party>
	<ID>1234</ID>
	<Type>Organisation</Type>
	<Entity>ABCD</Entity>
	<OrgType>Registered company</OrgType>
	<PartyId>
		<Design>7890</Design>
		<Name>ACN</Name>
	</PartyId>
	<TRDetail>
		<TRIndicator>yes</TRIndicator>
		<TRName>XYZ</TRName>
	</TRDetail>
</Party>
<Party>
	<ID>1234</ID>
	<Type>Organisation</Type>
	<Entity>ABCD</Entity>
	<OrgType>Registered company</OrgType>
	<PartyId>
		<Design>7890</Design>
		<Name>ACN</Name>
	</PartyId>
	<TRDetail>
		<TRIndicator>yes</TRIndicator>
		<TRName>XYZ</TRName>
	</TRDetail>
</Party>') from dual)
select distinct x.* 
from example,xmltable(
    '/Party/TRDetail'
    passing xmlfile
    columns
        trindicator varchar2(3)  path 'TRIndicator',
        trname      varchar2(20) path 'TRName'
) x

TRINDICATOR TRNAME              
----------- --------------------
yes         XYZ                 
1 row selected.
1 person found this helpful
1 - 3

Post Details

Added on Mar 9 2022
#oracle-developer-communities, #pl-sql-xml-programming, #xml
3 comments
1,122 views