Discussions
Categories
- 197K All Categories
- 2.5K Data
- 546 Big Data Appliance
- 1.9K Data Science
- 450.8K Databases
- 221.9K General Database Discussions
- 3.8K Java and JavaScript in the Database
- 31 Multilingual Engine
- 552 MySQL Community Space
- 479 NoSQL Database
- 7.9K Oracle Database Express Edition (XE)
- 3.1K ORDS, SODA & JSON in the Database
- 556 SQLcl
- 4K SQL Developer Data Modeler
- 187.2K SQL & PL/SQL
- 21.4K SQL Developer
- 296.4K Development
- 17 Developer Projects
- 139 Programming Languages
- 293.1K Development Tools
- 110 DevOps
- 3.1K QA/Testing
- 646.1K Java
- 28 Java Learning Subscription
- 37K Database Connectivity
- 161 Java Community Process
- 105 Java 25
- 22.1K Java APIs
- 138.2K Java Development Tools
- 165.3K Java EE (Java Enterprise Edition)
- 19 Java Essentials
- 162 Java 8 Questions
- 86K Java Programming
- 81 Java Puzzle Ball
- 65.1K New To Java
- 1.7K Training / Learning / Certification
- 13.8K Java HotSpot Virtual Machine
- 94.3K Java SE
- 13.8K Java Security
- 205 Java User Groups
- 24 JavaScript - Nashorn
- Programs
- 475 LiveLabs
- 39 Workshops
- 10.2K Software
- 6.7K Berkeley DB Family
- 3.5K JHeadstart
- 5.7K Other Languages
- 2.3K Chinese
- 175 Deutsche Oracle Community
- 1.1K Español
- 1.9K Japanese
- 233 Portuguese
How to get the element values from this XML using 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;
Best 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 SydneyEDIT:
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.
Answers
-
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 SydneyEDIT:
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.
-
Thanks it worked. So is it because of the namespace or the path and namespace?
-
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 //.