SQL & PL/SQL

Hello

I have an XML payload that contains only 2 sets of records. But in the final query it is resulting in duplicate records though DISTINCT keyword is used.

[code]

with t as (

select xmltype('<env:Envelope xmlns:env="http://schemas.xmlsoap.org/soap/envelope/" xmlns:wsa="http://www.w3.org/2005/08/addressing">

  <env:Header>

  <wsa:MessageID>urn:fa8a8e3b-6916-11e6-b819-00144ff95fcb</wsa:MessageID>

  <wsa:ReplyTo><wsa:Address>http://www.w3.org/2005/08/addressing/anonymous</wsa:Address>

  <wsa:ReferenceParameters>

  <instra:tracking.ecid xmlns:instra="http://xmlns.oracle.com/sca/tracking/1.0">7d8535e2-3f61-4963-a8cf-7417c14a3d55-000298d9</instra:tracking.ecid>

  <instra:tracking.FlowEventId xmlns:instra="http://xmlns.oracle.com/sca/tracking/1.0">2110695</instra:tracking.FlowEventId>

  <instra:tracking.FlowId xmlns:instra="http://xmlns.oracle.com/sca/tracking/1.0">881674</instra:tracking.FlowId>

  <instra:tracking.CorrelationFlowId xmlns:instra="http://xmlns.oracle.com/sca/tracking/1.0">0000LQqndlg6aMS_UDx0ic1NiqJC0000ME</instra:tracking.CorrelationFlowId>

  </wsa:ReferenceParameters>

  </wsa:ReplyTo>

  <wsa:FaultTo>

  <wsa:Address>http://www.w3.org/2005/08/addressing/anonymous</wsa:Address>

  </wsa:FaultTo>

  </env:Header>

<env:Body>

<Response xmlns:tns="http://www.test.co.uk/BAU/MeterReadsService/1.0" xmlns="http://www.test.co.uk/BAU/MeterReadsService/1.0">

  <tns:results>

    <tns:DeliveryPointDetails>

                 <tns:DeliveryPoint>1</tns:DeliveryPoint>

    <tns:MeterPointDetails>

                 <tns:MeterPoint>462810</tns:MeterPoint>

        <tns:MeterReadDetails>

              <tns:DateRead>2014-07-11T00:00:00.000+01:00</tns:DateRead>

          <tns:RegisterReadsDetails>

                <tns:PresentRead>25</tns:PresentRead>

                <tns:PreviousRead>0</tns:PreviousRead>

                <tns:ReadCode>1</tns:ReadCode>

                <tns:ReadCodeDescription>2</tns:ReadCodeDescription>

                <tns:ChargedVolume>25</tns:ChargedVolume>

                <tns:ReadType>FINAL</tns:ReadType>

                <tns:ReadTypeDescription>READ</tns:ReadTypeDescription>

        </tns:RegisterReadsDetails>

      </tns:MeterReadDetails>

    </tns:MeterPointDetails>

   </tns:DeliveryPointDetails>

  </tns:results>

  <tns:results>

      <tns:DeliveryPointDetails>

               <tns:DeliveryPoint>1</tns:DeliveryPoint>

     <tns:MeterPointDetails>

               <tns:MeterPoint>111778</tns:MeterPoint>

     <tns:MeterReadDetails>

              <tns:DateRead>1999-07-01T00:00:00.000+01:00</tns:DateRead>

           <tns:RegisterReadsDetails>

                <tns:PresentRead>43</tns:PresentRead>

                <tns:PreviousRead>24</tns:PreviousRead>

                <tns:ReadCode>1</tns:ReadCode>

                <tns:ReadCodeDescription>3</tns:ReadCodeDescription>

                <tns:ChargedVolume>19</tns:ChargedVolume>

                <tns:ReadType>BILL</tns:ReadType>

                <tns:ReadTypeDescription>READ</tns:ReadTypeDescription>

           </tns:RegisterReadsDetails>

       </tns:MeterReadDetails>

     </tns:MeterPointDetails>

   </tns:DeliveryPointDetails>

   </tns:results>

  <tns:Status>

    <tns:ErrorCode>S</tns:ErrorCode>

    <tns:ErrorType/>

    <tns:ErrorDetail/>

</tns:Status>

</Response>

</env:Body>

</env:Envelope>') as xmlcol from dual

)

     Select DISTINCT dlvPnt.*, mtrPnt.*,dtRead.*,rgstrMtrRead.*

       From t,

           Xmltable(

                 Xmlnamespaces

                  (

                    'http://schemas.xmlsoap.org/soap/envelope/' As "n1"

                   ,'http://www.test.co.uk/BAU/MeterReadsService/1.0' As "n2" 

                  ),

                  'n1:Envelope/n1:Body/n2:Response/n2:results/n2:DeliveryPointDetails' PASSING t.xmlcol

          Columns

            Deliverypoint    Varchar2(40) Path 'n2:DeliveryPoint'

        ) dlvPnt

      ,

        Xmltable(

                 Xmlnamespaces

                  (

                    'http://schemas.xmlsoap.org/soap/envelope/' As "n1"

                   ,'http://www.test.co.uk/BAU/MeterReadsService/1.0' As "n2" 

                  ),

                  'n1:Envelope/n1:Body/n2:Response/n2:results/n2:DeliveryPointDetails/n2:MeterPointDetails' PASSING t.xmlcol

          Columns

            Meterpoint       Varchar2(40) Path 'n2:MeterPoint'

        ) mtrPnt

      ,

        Xmltable(

                 Xmlnamespaces

                  (

                    'http://schemas.xmlsoap.org/soap/envelope/' As "n1"

                   ,'http://www.test.co.uk/BAU/MeterReadsService/1.0' As "n2" 

                  ),

              'n1:Envelope/n1:Body/n2:Response/n2:results/n2:DeliveryPointDetails/n2:MeterPointDetails/n2:MeterReadDetails' PASSING t.xmlcol

          Columns

            Dateread         Varchar2(40) Path 'n2:DateRead'

        ) dtRead

      ,

         Xmltable(

                 Xmlnamespaces

                  (

                    'http://schemas.xmlsoap.org/soap/envelope/' As "n1"

                   ,'http://www.test.co.uk/BAU/MeterReadsService/1.0' As "n2" 

                  ),

              'n1:Envelope/n1:Body/n2:Response/n2:results/n2:DeliveryPointDetails/n2:MeterPointDetails/n2:MeterReadDetails/n2:RegisterReadsDetails' PASSING t.xmlcol

          Columns

            PresentRead      varchar2(40) Path 'n2:PresentRead',

            PreviousRead     varchar2(40) Path 'n2:PreviousRead',

            ReadCode         varchar2(40) Path 'n2:ReadCode',

            ReadCodeDescription    varchar2(40) Path 'n2:ReadCodeDescription',

            ChargedVolume          varchar2(40) Path 'n2:ChargedVolume',

            Readtype               Varchar2(40) Path 'n2:ReadType',

            Readtypedescription    Varchar2(40) Path 'n2:ReadTypeDescription',

            Errorcode              Varchar2(1)    Path 'n2:ErrorCode',

            Errordetail            Varchar2(100)  Path 'n2:ErrorDetail',

            Faultcode              Varchar2(40)   Path './faultcode',

            FaultString            VARCHAR2(500)  path './faultstring'           

        ) Rgstrmtrread;

[/CODE]

Result is: Though a DISTINCT keyword is used, I see the data is repeated.

Anyone know how to fix this please?

Thanks in advance