Oracle Database XML Datatype Column Query with Xquery and Multiple Nested Namespaces
I wish I had to go down to the community with this, I tried this issue against this issue for a week, read and studied the Oracle documentation and these forums. The closest model answer I could get was this
I have this XML in oracle colum with XML datatype:
<TravelReservationRequest xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xmlns:xsd="http://www.w3.org/2001/XMLSchema" xmlns="http://schema.atravelcompany.com/TravelReservationTransaction" transactionType="TICKETED" creationDateTime="2015-06-30T21:01:09.0405878Z">
<CWTTravelReservation xmlns="http://schema.atravelcompany.com/TravelReservation">
<Reservation accountNumber="12345678" lastModifiedTimestamp="2015-06-30T21:01:09.0717888Z" recordLocatorNumber="ABCDEFG" supplierService="SUPPLIER" reservationType="Business" InternationalFlag="false" bookingDate="2015-06-30">
<VendorList>
<Vendor vendorCode="AD" vendorClassCode="1" vendorClassification="Air"/>
<Vendor vendorCode="RT" vendorClassCode="3" vendorName="SOME HOTEL SOMEWHERE" vendorClassification="Hotel"/>
</VendorList>
<TravelerList>
<Traveler sequence="1" guid="A:GUID">
<Person birthDate="XXXX-XX-XX">
<Name xmlns="http://schema.atravelcompany.com/V1" namePrefix="MR" firstName="JOHN " lastName="DOE"/>
</Person>
<TravelerAddress type="Alternate" line1="A HOUSE" stateProvince="A PLACE"/>
<Client>
<ClientTop xmlns="http://schema.atravelcompany.com/V1" guid="ANOTHER:GUID"/>
<ClientSub xmlns="http://schema.atravelcompany.com/V1"/>
</Client>
<TravelerEmail emailAddress="email@address.com"/>
<TravelerPhone usage="Mobile"/>
<TravelerType description="SOMETHING"/>
<EmergencyContactList>
<EmergencyContact>
<EmergencyContactPhone/>
<EmergencyContactEmail/>
<UnparsedGDSContent/>
</EmergencyContact>
</EmergencyContactList>
<PassportList>
<Passport xmlns="http://schema.atravelcompany.com/V1" number="XXXXXXXX" issuingCountry="BR"/>
</PassportList>
</Traveler>
</TravelerList>
<SegmentGroupList>
<SegmentGroup lowFareAmount="0">
<SegmentList>
<Segment segmentEndTimestamp="2015-07-01T07:15:00" segmentTravelDurationValue="0117" segmentTypeDescription="Air" segmentBeginTimestamp="2015-07-01T05:58:00" vendorCode="AD" vendorClassCode="1" supplierStatusCode="YK" confirmationNumber="SOMETHING" segmentNumber="1" manualBookingFlag="false">
<SegmentTravelerList>
<SegmentTraveler travelerIdRef="1"/>
</SegmentTravelerList>
<AirSegment aircraftTypeCode="E90" beginAirportCode="VCP" endAirportCode="FLN" classOfServiceCode="V" connectionType="false" eTicketFlag="true" flightNumber="4050" inFlightMealDescription="" travelDistanceValue="320">
<CodeShare/>
</AirSegment>
</Segment>
<Segment segmentEndTimestamp="2015-07-03T12:38:00" segmentTravelDurationValue="0120" segmentTypeDescription="Air" segmentBeginTimestamp="2015-07-03T11:18:00" vendorCode="AD" vendorClassCode="1" supplierStatusCode="YK" confirmationNumber="SOMETHING" segmentNumber="3" manualBookingFlag="false">
<SegmentTravelerList>
<SegmentTraveler travelerIdRef="1"/>
</SegmentTravelerList>
<AirSegment aircraftTypeCode="E90" beginAirportCode="A" endAirportCode="B" classOfServiceCode="G" connectionType="false" eTicketFlag="true" flightNumber="4064" inFlightMealDescription="" travelDistanceValue="320">
<CodeShare/>
</AirSegment>
</Segment>
</SegmentList>
<InvoiceList>
<Invoice ticketNumber="8003674469" travelerRefId="1" exchangeTransactionFlag="false"/>
</InvoiceList>
</SegmentGroup>
<SegmentGroup>
<SegmentList>
<Segment segmentEndTimestamp="2015-07-03T11:18:00" segmentTypeDescription="Hotel" reservedUnitQuantity="1" segmentBeginTimestamp="2015-07-01T07:15:00" vendorCode="RT" vendorClassCode="3" supplierStatusCode="HK" confirmationNumber="000000000" currencyCode="BRL" segmentNumber="2" rateFareAmount="218.70" manualBookingFlag="false">
<HotelSegment cityCode="C" hotelPropertyCode="1234" propertyName="A HOTEL" roomAdultQuantity="1" roomTypeCode="C2T01J">
<HotelAddressList>
<HotelAddress type="Alternate" line1="AN ADDRESS"/>
</HotelAddressList>
<HotelEmailAddress/>
<HotelFaxNumber phoneNumber="1234"/>
<HotelPhoneNumber phoneNumber="1234"/>
</HotelSegment>
</Segment>
</SegmentList>
</SegmentGroup>
</SegmentGroupList>
<ReservationPCCList>
<ReservationPCC pseudoCityCode="CI6C" supplierService="Sabre" pseudoCityType="Booking"/>
</ReservationPCCList>
<BookingAgencyList>
<BookingAgency>
<AgencyPhoneList>
<AgencyPhone phoneNumber="321321321321"/>
</AgencyPhoneList>
</BookingAgency>
</BookingAgencyList>
</Reservation>
</CWTTravelReservation>
</TravelReservationRequest>
I need to be able to retrieve values and possibly lists of values from this XML using something like a request in the model response, but try my best, I can't get this to work and I need to hold my hands a little.
My last attempt looks like this by example and just doesn't work at all.
select t.*
from
sch_edw_stg.mdf_audit a,
xmltable(xmlnamespaces('http://schema.acompany.com/V1' as "v1"
,'http://schema.acompany.com/TravelReservation' as "resr"
,'http://schema.acompany.com/TravelReservationTransaction' as "trans"
,'http://www.w3.org/2001/XMLSchema' as "xsd"
,'http://www.w3.org/2001/XMLSchema-instance' as "xsi")
,'for $d in resr:/CWTTravelReservation:Value
return $d' passing a.mdf_xml_text columns value varchar2(100) path '/') as t;
and I tried many other permutations and examples from here and elsewhere. The best I can do is return two null values, and I'm pretty sure this doesn't allow for the exact identification of the node I need relative to the XML base.
This is the code that returns a bunch of zeros, but at least it works:
SELECT xt.*
FROM sch_edw_stg.mdf_audit x,
XMLTABLE(XMLNAMESPACES ('http://schema.acompany.com/TravelReservationTransaction' AS "e"),'/'
PASSING x.mdf_xml_text
COLUMNS
"VENDORCODE" VARCHAR2(255) PATH 'e:vendorCode',
"VENDORCLASSCODE" VARCHAR2(255) PATH 'e:vendorClassCode',
"VENDORCLASSIFICATION" VARCHAR2(255) PATH 'e:vendorClassification',
"VENDORNAME" VARCHAR2(255) PATH 'e:vendorName'
) xt;
Can anyone give me a working xquery example based on this XML and give me some pointers as to why my attempts are not working?
Thank.
Ed.
source to share
You need to pass the full path to the provider nodes, including specifying the appropriate namespace at each level:
/trans:TravelReservationRequest/resr:CWTTravelReservation/resr:Reservation/resr:VendorList/resr:Vendor
And you are accessing the attributes of these nodes, so you need to use the syntax @
to see them in your PATH
:
select t.*
from mdf_audit a
cross join xmltable(
xmlnamespaces('http://schema.atravelcompany.com/V1' as "v1"
,'http://schema.atravelcompany.com/TravelReservation' as "resr"
,'http://schema.atravelcompany.com/TravelReservationTransaction' as "trans"
,'http://www.w3.org/2001/XMLSchema' as "xsd"
,'http://www.w3.org/2001/XMLSchema-instance' as "xsi")
,'/trans:TravelReservationRequest/resr:CWTTravelReservation/resr:Reservation/resr:VendorList/resr:Vendor'
passing a.mdf_xml_text
columns
"VENDORCODE" VARCHAR2(255) PATH '@vendorCode',
"VENDORCLASSCODE" VARCHAR2(255) PATH '@vendorClassCode',
"VENDORCLASSIFICATION" VARCHAR2(255) PATH '@vendorClassification',
"VENDORNAME" VARCHAR2(255) PATH '@vendorName'
) t;
VENDORCODE VENDORCLAS VENDORCLAS VENDORNAME
---------- ---------- ---------- ------------------------------
AD 1 Air
RT 3 Hotel SOME HOTEL SOMEWHERE
In your last attempt, it is resr:/CWTTravelReservation:Value
simply distorted. In working-but-zero, you only look at the top level TravelReservationRequest
, which has no named child nodes vendorCode
, etc., hence zero results. And you are looking for nodes, not attributes anyway, with this path syntax. (You also have a discrepancy between atravelcompany.com
and acompany.com
, but I suspect you pretend you are shaping the question and hiding the real values.)
source to share