Which String functions to use?

H

h2fcell

Hello,
Thought I’d post this question before I tinker with it myself.
I have a field “details_xml†that contains xml information like the below
example.

<TravelAvailDetails><Hotel Address1="9, Rue Littre" City="Paris"
Country="France" GroupCode="PAR" HotelId="Paris-Littre" HotelName="Littre
Hotel" ProductType="" Rating="4stars" Zip="75006"><Rate AllotmentCode=""
Commission="179.10" ConditionsCode="France-Condition1" Deposit="0.00"
Description="Triple Standard Room with BB Meal Plan"
Email="(e-mail address removed)" ExternalParameters=""
FinalPaymentDueDate="2009-06-18" GrossPrice="1791.00"
InitialCommunicationDatetime="2009-04-06T10:24:03" InventorySourceVendor=""
IsMerchantModel="true" NetNetPrice="1306.98" NetPrice="1611.90"
PaymentDueDays="0" PurchasingCurrency="EUR"
PurchasingCurrencyNetNetPrice="954.00" RateId="STD-BB-TPL"
ReferenceName="CHERYL WALL" SellAtGross="true" Status="On Request"
SupplierID="FRCDGLTERIN" Tax="0.00"><Room ConditionCode="France-Condition1"
RoomNo="0"><BaseRateCategorization Code="STD-BB-TPL" Commission="179.10"
Description="Triple Standard Room with BB Meal Plan" Gross="1791.00"
Net="1611.90" NetNet="1306.98" PurchasingNetNet="954.00"
Tax="0.00"/></Room><KeyValues/></Rate><KeyValues/><Confirmation
Date="4/7/2009" Number="RT 316055" OtherInfo1="" OtherInfo2=""
Status="1"/></Hotel><RoomRequests><RoomRequest NumberOfAdults="3"
NumberOfChildren="0"/></RoomRequests><HotelReservationResponse
ErrorMessage="" Key="HI2000000000038064994" Pnr="ON REQ" Reserved="true"
ReturnCode="0"/><TravelDocumentInformation VoucherCode="AUT"
VoucherGeneratedOn="2009-05-29T16:45:57"
VoucherNumber="209603"/></TravelAvailDetails>

Using Access 2007, I need to create a query that creates a field based on
information in field “details_xmlâ€.
Specifically, I need to find HotelName and get its value. For the above
example it would be, Littre Hotel.
I’m thinking a combination of string functions, but maybe there’s any easier
way.
Any suggestions are well appreciated.
Thanks.
 
K

KARL DEWEY

Try this --
Mid([details_xml], InStr([details_xml], "HotelName=")+1,
InStr(InStr([details_xml], "HotelName=")+1, "ProductType=")-2)
 
V

vanderghast

Can try MSXML. I am far from being an expert at it, but first, you load the
'xml document' (as a string, in your case) :

--------------------------
Dim xmlDoc As New Msxml2.DOMDocument30
xmlDoc.async = False
xmlDoc.loadXML ("<customer>" & _
"<first_name>Joe</first_name>" & _
"<last_name>Smith</last_name></customer>")
If (xmlDoc.parseError.errorCode <> 0) Then
MsgBox("You have error " & xmlDoc.parseError)
End If
-----------------------------

and then, reach a node, and its attribute:

------------------------------
Dim nodeBook As IXMLDOMElement
Dim sIdValue As String
Set nodeBook = xmlDoc.selectSingleNode("//book")
sIdValue = nodeBook.getAttribute("id")
------------------------------

or something along the same line.


Using the xml parser rather than a scan for a fix string is more flexible
since the scan for the string may depends on how the xml string is built.
But if the document is always produced the same way, then a scan for a
string may be quite nice too.


Vanderghast, Access MVP
 
H

h2fcell

Thanks,
I'm using the following string function combination.

DPHotelKey:
Mid$([travel_avail_details_xml],(InStr([travel_avail_details_xml],"Key=")+5),21)
 
Top