Forum Stats

  • 3,839,830 Users
  • 2,262,539 Discussions
  • 7,901,065 Comments

Discussions

How to get the element values from this XML using SQL

pkpanda
pkpanda Member Posts: 346 Bronze Badge
edited Jan 10, 2020 5:59AM in SQL & PL/SQL
<?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;
Tagged:
Gaz in Oz_jumpkpanda

Best Answer

  • mNem
    mNem Member Posts: 1,380 Gold Trophy
    edited Jan 10, 2020 2:37AM Answer ✓

    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.

    Gaz in Oz_jum

Answers

  • mNem
    mNem Member Posts: 1,380 Gold Trophy
    edited Jan 10, 2020 2:37AM Answer ✓

    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.

    Gaz in Oz_jum
  • pkpanda
    pkpanda Member Posts: 346 Bronze Badge
    edited Jan 10, 2020 5:29AM

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

  • mNem
    mNem Member Posts: 1,380 Gold Trophy
    edited Jan 10, 2020 5:59AM

    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 //.

    pkpanda