How to get the element values from this XML using SQL

pkpanda

<?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;

mNem

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.* from xmldata, xmltable(

xmlnamespaces (default 'urn:PabaWebServices/XYZ_abcd/'),

'

/*:schema/customerDetails_v1Response/customerDetails_v1Result/AddressHistory

'

passing xmlData.xml
columns
  dateChanged         varchar2(50) path 'dateChanged'
, displayEventType    varchar2(50) path 'displayEventType'
, eventDate           varchar2(50) path 'eventDate'
, propertyName        varchar2(50) path 'propertyName'
, street              varchar2(50) path 'street'
, suburb              varchar2(50) path 'suburb'
, vRegion             varchar2(50) path 'vRegion'
) x
;

DATECHANGED                                        DISPLAYEVENTTYPE                                   EVENTDATE                                          PROPERTYNAME                                       STREET                                             SUBURB                                             VREGION                                           
-------------------------------------------------- -------------------------------------------------- -------------------------------------------------- -------------------------------------------------- -------------------------------------------------- -------------------------------------------------- --------------------------------------------------
2019-04-06T10:32:03.525-00:00                      Address                                            2019-04-06                                                                                            BURNE ROAD                                         TEST ME ST                                         Sydney                                            

EDIT:

You may go specific with type for columns ...

columns

  dateChanged         timestamp with time zone path 'dateChanged'

, displayEventType    varchar2(50)             path 'displayEventType'

, ensDate             date                     path 'endDate'

, eventDate           date                     path 'eventDate'

, propertyName        varchar2(50)             path 'propertyName'

, nmbr                int                      path 'number'

...

Another Edit:

Removed the unnecessary namespace.

mNem

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.* from xmldata, xmltable(

xmlnamespaces (default 'urn:PabaWebServices/XYZ_abcd/'),

'

/*:schema/customerDetails_v1Response/customerDetails_v1Result/AddressHistory

'

passing xmlData.xml
columns
  dateChanged         varchar2(50) path 'dateChanged'
, displayEventType    varchar2(50) path 'displayEventType'
, eventDate           varchar2(50) path 'eventDate'
, propertyName        varchar2(50) path 'propertyName'
, street              varchar2(50) path 'street'
, suburb              varchar2(50) path 'suburb'
, vRegion             varchar2(50) path 'vRegion'
) x
;

DATECHANGED                                        DISPLAYEVENTTYPE                                   EVENTDATE                                          PROPERTYNAME                                       STREET                                             SUBURB                                             VREGION                                           
-------------------------------------------------- -------------------------------------------------- -------------------------------------------------- -------------------------------------------------- -------------------------------------------------- -------------------------------------------------- --------------------------------------------------
2019-04-06T10:32:03.525-00:00                      Address                                            2019-04-06                                                                                            BURNE ROAD                                         TEST ME ST                                         Sydney                                            

EDIT:

You may go specific with type for columns ...

columns

  dateChanged         timestamp with time zone path 'dateChanged'

, displayEventType    varchar2(50)             path 'displayEventType'

, ensDate             date                     path 'endDate'

, eventDate           date                     path 'eventDate'

, propertyName        varchar2(50)             path 'propertyName'

, nmbr                int                      path 'number'

...

Another Edit:

Removed the unnecessary namespace.

pkpanda

Thanks it worked. So is it because of the namespace or the path and namespace?

mNem

It was the missing namespace, not returning you the results.

- Avoid the extract() method as it is deprecated.

- For efficiency, usage of absolute path is preferred over shortened //.