<?xml version="1.0" encoding="utf-8"?>
<schema xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xmlns:xsd="http://www.w3.org/2001/XMLSchema" xmlns:soap="http://schemas.xmlsoap.org/soap/envelope/">
<customerDetails_v1Response xmlns="urn:PabaWebServices/XYZ_abcd/">
<customerDetails_v1Result xsi:type="WS_customerDetailsResponse">
<allErrors xsi:nil="true"/>
<AddressHistory>
<dateChanged>2019-04-06T10:32:03.525-00:00</dateChanged>
<displayEventType>Address</displayEventType>
<endDate>1900-01-01</endDate>
<eventDate>2019-04-06</eventDate>
<number>88</number>
<postCode>2000</postCode>
<propertyName/>
<street>BURNE ROAD</street>
<suburb>TEST ME ST</suburb>
<town>SYDNEY</town>
<unit/>
<vRegion>Sydney</vRegion>
</AddressHistory>
<customer>
<customerId>ABC1234567</customerId>
<switchIn>false</switchIn>
</customer>
</customerDetails_v1Result>
<message>customer Details found</message>
</customerDetails_v1Response>
</schema>
The sql is, I have tried with various combination of the path but it still not returning any values.
WITH xmlData
AS (SELECT XMLType ('<?xml version="1.0" encoding="utf-8"?>
<schema xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xmlns:xsd="http://www.w3.org/2001/XMLSchema" xmlns:soap="http://schemas.xmlsoap.org/soap/envelope/">
<customerDetails_v1Response xmlns="urn:PabaWebServices/XYZ_abcd/">
<customerDetails_v1Result xsi:type="WS_customerDetailsResponse">
<allErrors xsi:nil="true"/>
<AddressHistory>
<dateChanged>2019-04-06T10:32:03.525-00:00</dateChanged>
<displayEventType>Address</displayEventType>
<endDate>1900-01-01</endDate>
<eventDate>2019-04-06</eventDate>
<number>88</number>
<postCode>2000</postCode>
<propertyName/>
<street>BURNE ROAD</street>
<suburb>TEST ME ST</suburb>
<town>SYDNEY</town>
<unit/>
<vRegion>Sydney</vRegion>
</AddressHistory>
<customer>
<customerId>ABC1234567</customerId>
<switchIn>false</switchIn>
</customer>
</customerDetails_v1Result>
<message>customer Details found</message>
</customerDetails_v1Response>
</schema> ') AS xml FROM dual)
SELECT x.xml.EXTRACT ('/customerDetails_v1Response/customerDetails_v1Result/AddressHistory/dateChanged/text()').getStringVal () dateChanged,
x.xml.EXTRACT ('/AddressHistory/displayEventType/text()').getStringVal () displayEventType,
x.xml.EXTRACT ('//AddressHistory/endDate/text()').getStringVal () endDate,
x.xml.EXTRACT ('//AddressHistory/eventDate/text()').getStringVal () eventDate,
x.xml.EXTRACT ('//AddressHistory/postCode/text()').getStringVal () postCode,
x.xml.EXTRACT ('//AddressHistory/propertyName/text()').getStringVal () propertyName,
x.xml.EXTRACT ('//AddressHistory/street/text()').getStringVal () street,
x.xml.EXTRACT ('//AddressHistory/suburb/text()').getStringVal () suburb,
x.xml.EXTRACT ('//AddressHistory/town/text()').getStringVal () town,
x.xml.EXTRACT ('//AddressHistory/unit/text()').getStringVal () unit,
x.xml.EXTRACT ('//AddressHistory/vRegion/text()').getStringVal () vRegion
FROM xmlData x;