How to remove duplicate values from XMLTYPE?

User_2K4XP

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

We are using Oracle Database 19c .

Thanks!

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.