XML Extract

with sss as (

    select XMLType(

  '<Charges>

        <ChargeInfo>

           <AmountToPay>1000</AmountToPay>

        </ChargeInfo>

        <ChargeInfo>

           <AmountToPay>2000</AmountToPay>

        </ChargeInfo>

  </Charges>'

           ) xml from dual

) select extractValue(value(t2),'ChargeInfo/AmountToPay') AmountToPay

  from sss t1,

       table(XMLSequence(t1.xml.extract('Charges/ChargeInfo'))) t2

with sss as (

    select XMLType(

  '<Charges>

        <ChargeInfo>

           <Charge Id="ID_1"/>

        </ChargeInfo>

        <ChargeInfo>

           <Charge Id="ID_2"/>

        </ChargeInfo>

  </Charges>'

           ) xml from dual

) select extractValue(value(t2),'ChargeInfo/Charge/@Id') Id

  from sss t1,

       table(XMLSequence(t1.xml.extract('Charges/ChargeInfo'))) t2

with sss as (

    select XMLType(

  '<Charges>

        <ChargeInfo>

           <AmountToPay>1000</AmountToPay>

           <Charge Id="ID_1"/>

        </ChargeInfo>

        <ChargeInfo>

           <AmountToPay>2000</AmountToPay>

           <Charge Id="ID_2"/>

        </ChargeInfo>

  </Charges>'

           ) xml from dual

) select extractValue(value(t2),'ChargeInfo/AmountToPay') AmountToPay

         ,extractValue(value(t2),'ChargeInfo/Charge/@Id') Id

  from sss t1,

       table(XMLSequence(t1.xml.extract('Charges/ChargeInfo'))) t2