Forum Stats

  • 3,827,255 Users
  • 2,260,751 Discussions
  • 7,897,192 Comments

Discussions

How to remove duplicate values from XMLTYPE?

User_2K4XP
User_2K4XP Member Posts: 5 Red Ribbon

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!

Answers

  • cormaco
    cormaco Member Posts: 1,943 Silver Crown

    Please post your example XML as actual text.

  • User_2K4XP
    User_2K4XP Member Posts: 5 Red Ribbon

    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
    cormaco Member Posts: 1,943 Silver Crown

    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.
    
    User_2K4XP