SQL & PL/SQL

Hi All,

Need to extract specific data from xml format data, stored in a clob column using sql.

-----------------------------------------------------Sample data-------------------------------------------------------------------------------------------------------------------------------------------

create table xx_custom (payload clob);

insert into xx_custom values

             ('<env:Envelope xmlns:env="http://schemas.xmlsoap.org/soap/envelope/" xmlns:wsa="http://www.w3.org/2005/08/addressing"><env:Header><wsa:Action>http://xmlns.oracle.com/apps/scm/inventory/materialAvailability/onhandQuantities/onhandQuantityServiceV2//OnhandQuantityService/queryAllInventoryResponse</wsa:Action><wsa:MessageID>urn:uuid:e839b82c-c4e7-422f-a762-db4a947b7fc0</wsa:MessageID></env:Header><env:Body><ns0:queryAllInventoryResponse xmlns:ns0="http://xmlns.oracle.com/apps/scm/inventory/materialAvailability/onhandQuantities/onhandQuantityServiceV2/types/"><ns1:result xmlns:ns1="http://xmlns.oracle.com/apps/scm/inventory/materialAvailability/onhandQuantities/onhandQuantityServiceV2/types/" xmlns:ns0="http://xmlns.oracle.com/apps/scm/inventory/materialAvailability/onhandQuantities/onhandQuantityServiceV2/" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xsi:type="ns0:InvQuantity"><ns0:QuantityOnHand>51</ns0:QuantityOnHand><ns0:ReservableQuantityOnHand>51</ns0:ReservableQuantityOnHand><ns0:QuantityReserved>0</ns0:QuantityReserved><ns0:QuantitySuggested>0</ns0:QuantitySuggested><ns0:AvailableToTransact>40</ns0:AvailableToTransact><ns0:AvailableToReserve>40</ns0:AvailableToReserve><ns0:SecondaryQuantityOnHand>0</ns0:SecondaryQuantityOnHand><ns0:SecondaryReservableQuantityOnHand>0</ns0:SecondaryReservableQuantityOnHand><ns0:SecondaryQuantityReserved>0</ns0:SecondaryQuantityReserved><ns0:SecondaryQuantitySuggested>0</ns0:SecondaryQuantitySuggested><ns0:SecondaryAvailableToTransact>0</ns0:SecondaryAvailableToTransact><ns0:SecondaryAvailableToReserve>0</ns0:SecondaryAvailableToReserve></ns1:result></ns0:queryAllInventoryResponse></env:Body></env:Envelope>');

-------------------------------------------------------------------------------------------expected result--------------------------------------------------------------------------------------------------------------------

Thanks in Advance!

Hitesh