Skip repeated values whilst XML parsing using PL/SQL

Question

I have the following XML structure that I have pulled from a web service and stored in a XMLTYPE variable in PL/SQL:

     <results>
        <return>
            <customerid>127</customerid>
            <customername>Test1</customername>
        </return>
        <return>
            <customerid>127</customerid>
            <customername>Test1</customername></return>
        <return>
            <customerid>93</customerid>
            <customername>Test2</customername>
        </return>
        <return>
            <customerid>96</customerid>
            <customername>Test3</customername>
        </return>
     </results>

Due to the inflexibility of the web service I am using, there is no way of specifying that only 1 occurrence of a customerid must be displayed with it's corresponding value. With that in mind, how can I parse this XML document using PL/SQL to retrieve only the first occurrence of each customer id?

For example, parsing the above would return:

       127 Test1
       93 Test2
       96 Test3

My idea was to begin parsing the XML and query against an empty array as to whether or not the customer id exists (has already been printed to screen). If not, then print the customer id an matching name to screen and add the customer id to the array. This would mean that the next time around that XML tag would be skipped and it would move onto the next one, at least that is what I could do in PHP but sadly this time around I have to use PL/SQL.

Any ideas on the most efficient way to do this in PL/SQL?

Cheers,

Jason

Additional: If necessary, I can change the structure of the XML document as I am generating it myself on the fly using PL/SQL.

Answer 1

Extract all nodes to dataset and select distinct values:

SQLFiddle

with params as (
  select 
    xmltype('  
     <results>
        <return>
            <customerid>127</customerid>
            <customername>Test1</customername>
        </return>
        <return>
            <customerid>127</customerid>
            <customername>Test1</customername></return>
        <return>
            <customerid>93</customerid>
            <customername>Test2</customername>
        </return>
        <return>
            <customerid>96</customerid>
            <customername>Test3</customername>
        </return>
     </results>
   ') p_xml 
 from dual
) 
select 
  distinct customer_id, customer_name 
from 
  XMLTable(
    '
      for $i in $doc/results/return     
        return $i
    '
    passing (select p_xml from params) as "doc"
    columns
      customer_id   number         path '//customerid',
      customer_name varchar2(4000) path '//customername'
  )

Another way to parse:

SQLFiddle

with params as (
  select 
    sys.xmltype('  
     <results>
        <return>
            <customerid>127</customerid>
            <customername>Test1</customername>
        </return>
        <return>
            <customerid>127</customerid>
            <customername>Test1</customername></return>
        <return>
            <customerid>93</customerid>
            <customername>Test2</customername>
        </return>
        <return>
            <customerid>96</customerid>
            <customername>Test3</customername>
        </return>
     </results>
   ') p_x 
 from dual
) 
select distinct
 extractvalue(column_value, '//customerid') customer_id,
 extractvalue(column_value, '//customername') customer_name
from 
  table(XMLSequence((select extract(p_x, '/results/return') x from params)))

Reconstruct XML from dataset if needed:

SQLFiddle

with params as (
  select 
    sys.xmltype('  
      -- same XML text as in previous examples ------- 
   ') p_x 
 from dual
) 
select 
  XMLElement("results",
    XMLAgg(
      XMLElement("return",
        XMLConcat(
          XMLElement("customerid", customer_id),
          XMLElement("customername", customer_name)
        )
      )
    )           
  ).getclobval()  
from (  
  select distinct
   extractvalue(column_value, '//customerid') customer_id,
   extractvalue(column_value, '//customername') customer_name
  from 
    table(XMLSequence((select extract(p_x, '/results/return') x from params)))
)