Sorry the above input file is not 100% correct. Following is the one, I'm using as input:
Code:
<?xml version="1.0" encoding="UTF-8"?>
<OTA_TT_HotelResRS xmlns="http://www.opentravel.org/OTA/2003/05" TimeStamp="2008-05-27T10:09:09" Target="Production" Version="1" ResResponseType="">
<Success/>
<HotelReservations>
<HotelReservation>
<RoomStays>
<RoomStay>
<RoomTypes>
<RoomType RoomTypeCode="144HB" NumberOfUnits="1">
<RoomDescription>
<Text>Twin Side Seaview - Half board</Text>
</RoomDescription>
</RoomType>
</RoomTypes>
<RoomRates>
<RoomRate RoomTypeCode="144HB" NumberOfUnits="1">
<Rates>
<Rate>
<Total AmountAfterTax="232.82" CurrencyCode="GBP"/>
</Rate>
</Rates>
</RoomRate>
</RoomRates>
<GuestCounts IsPerRoom="true">
<GuestCount AgeQualifyingCode="10" Count="2"/>
<GuestCount AgeQualifyingCode="8" Count="1"/>
<GuestCount AgeQualifyingCode="7" Count="1"/>
</GuestCounts>
<TimeSpan Start="2008-10-12" Duration="P3N" End="2008-10-15"/>
<BasicPropertyInfo HotelCode="6568" HotelName="Kapetanios Bay Hotel">
<VendorMessages>
<VendorMessage InfoType="4">
<SubSection>
<Paragraph>
<Text>Please collect your keys from reception</Text>
</Paragraph>
</SubSection>
</VendorMessage>
</VendorMessages>
<Address FormattedInd="false" DefaultInd="false">
<AddressLine>POBox 33142</AddressLine>
<AddressLine>Paralimni</AddressLine>
<PostalCode>5311</PostalCode>
<CountryName>Cyprus</CountryName>
</Address>
<ContactNumbers>
<ContactNumber PhoneLocationType="4" PhoneTechType="1" PhoneNumber="00357 23 831 170" FormattedInd="false" DefaultInd="false"/>
</ContactNumbers>
</BasicPropertyInfo>
</RoomStay>
</RoomStays>
<ResGuests>
<ResGuest>
<Profiles>
<ProfileInfo>
<Profile ShareAllSynchInd="No" ShareAllMarketInd="No">
<Customer>
<PersonName>
<GivenName>A</GivenName>
<Surname>Test</Surname>
<NameTitle>MR</NameTitle>
</PersonName>
<Address FormattedInd="false" DefaultInd="false">
<AddressLine>Test Street</AddressLine>
<CityName>Test City</CityName>
<PostalCode>TE5 T11</PostalCode>
</Address>
</Customer>
</Profile>
</ProfileInfo>
</Profiles>
</ResGuest>
</ResGuests>
<ResGlobalInfo>
<HotelReservationIDs>
<HotelReservationID ResID_Type="23" ResID_Value=""/>
</HotelReservationIDs>
</ResGlobalInfo>
</HotelReservation>
</HotelReservations>
</OTA_TT_HotelResRS>
Transformation script is mainly responsible for generating an insert script, which requires some change to handle blob object as well. I think the script should generate the query execution code as well..through java statement as part of xslt script, but i'm not 100% sure how.