April 13, 2009 at 7:07 pm
Can someone help me out here, what am I missing? I'm having trouble figuring out how to use XQuery over an xml document. The document is valid based on the schema I added to the schema_collection. This is a simple, thorough example. Sorry for the long winded schema.
SQL2K5 server using SQL2K8 SSMS
--* Drop the table
IF EXISTS(SELECT * FROM sys.objects WHERE object_id = OBJECT_ID('dbo.ResDataXml') AND type IN ('U'))
DROP TABLE dbo.ResDataXml
GO
--* Drop the xsd
IF EXISTS (SELECT * FROM sys.xml_schema_collections c, sys.schemas s
WHERE c.schema_id = s.schema_id AND (quotename(s.name) + '.' +
quotename(c.name)) = N'[dbo].[ota_ResNotifRQ_xsd]')
DROP XML SCHEMA COLLECTION [dbo].[ota_ResNotifRQ_xsd]
GO
--* Create the xsd
/*
CREATE XML SCHEMA COLLECTION OTA_ResNotifRQ_xsd AS
'
Month, day, year and optionally in hour, minute, second of day in ISO 8601 format of the action taken on the profile.
Month, day, year and optionally hour, minute, second of day the profile originated, in ISO 8601 format.
Identification of a company that participates in an alliance or consortium to which the primary
business entity identified in this profile belongs.
Identifies a rate for paying commissions; can be a decimal value based on percentage paid
for the commission plan, or a flat rate.
Refer to OTA Code List Travel Purpose (TVP).
A collection of Profile objects or Unique IDs of Profiles.
Refer to OTA Code List Fare Restriction (FAR).
A date that is associated to the fare restriction.
Indicates preferences for certain types of flights, such as connections or stopovers, when used for a specific travel situation.
Identifies the customers level of preference for the hotel food service identified.
Candidate for removal, usage is not recommended. Deprecation Warning added in 2006A. Removal intended for 2006B.
Identifies the customers level of preference for the hotel physically challenged feature identified.
Identifies the customers level of preference for the hotel recreational service identified.
A collection of Membership objects. Memberships provides a list of reward programs which may be credited with points accrued from the guests activity. Which memberships are to be applied to which part is determined by each objects SelectedMembershipRPHs collection.
A collection of reference place holders. This is a reference placeholder, used as an index for the reservation guests.
An enumerated type that defines the status of the reservation for this service.
Whether the price for this service is guaranteed or quoted and subject to change.
Values: False (Price NOT guaranteed) and True (Price is guaranteed).
Whether the price for this service is included in the room rate.
Values: False (or No), and True (or Yes).
Refer to OTA Code List Hotel Amenity Code (HAC).
A collection of Comment objects. Comments which apply to any part of the reservation (the reservation in its entirety, one or more guests,
one or more services, or one or more roomstays). Which comments apply to which part is determined by each objects ResCommentsRPHs collection.
CommentOriginatorCode : String
Unique identifier for the system which created the comment.
GuestViewable : Boolean
Whether or not this comment should be shown to the guest.
Values: False or No, and True or Yes.
A collection of Membership objects. Memberships provides a list of reward programs which may be credited with points accrued from the guests activity. Which memberships are to be applied to which part is determined by each objects SelectedMembershipRPHs collection.
The total number of points earned through the selected membership.
A text field used to provide a special ID code that is associated with the rate and is required in the reservation request in order to obtain the rate. Examples are a corporate ID number, a promotion code or a membership number.
This defines the form of payment. Recommended usage of this is with the Payment Type in OTA Code List, this datatype will be updated in the future.
In order to maintain forward compatability a change is not being made in this publication. This will be corrected in a future version, when a major update is released.
Refer to OTA Code List Room Amenity Type (RMA).
TimeStamp : TimeInstant
The date and time that the reservation passed through a routing hop.
Data : String
This attribute is provided so that each system can put in whatever data it would like (e.g., auditing information).
The RatePlanCode assigned by the receiving system for the inventory item in response to a new rate plan notification. (Implementation Notes: This would only be returned when the notification is of type New and the sender is translating RatePlanCode values.
On subsequent transactions for this rate plan, the sender would populate the RatePlanCode attribute with this value returned by the receiver.)
Refer to OTA Code List Travel Purpose (TVP).
Indicates the preferences for information about pets that accompany the customer in a given travel situation.
Ticket distribution method such as Fax, Email, Courier, Mail, Airport_Pickup, City_Office, Hotel_Desk,
WillCall, etc. Refer to OTA Code List Distribution Type (DTB).
Typically used to add an item where it does not exist or to update an item where it does exist.
Typically used to cancel an existing item.
Typically used to remove specified data.
Typically used to add data whether data already exists or not.
Typically used to overlay existing data.
Used to indicate if an amount is inclusive or exclusive of other charges, such as taxes, or is cumulative (amounts have been added to each other).
AVML - Asian Veg
BBML - Baby/Infant Food
BLML - Bland Meal
CHML - Child Meal
DBML - Diabetic
FPML - Fruit Meal
GFML - Gluten Free
HFML - High Fiber
HNML - Hindu Meal
KSML - Kosher
LCML - Low Calorie
LFML - Low Cholesterol
LPML - Low Protein
LSML - Low Sodium/No Salt
MOML - Moslem
NLML - Non-Lactose
ORML - Oriental
PRML - Low Purin
RVML - Raw Vegetarian
SFML - Seafood
SPML - Special/Specify
VGML - Vegetarian/Non Dairy
VLML - Vegetarian/Milk/Eggs
American Express
Bank Card
Carte Bleu
Carte Blanche
Diners Club
Discover Card
Eurocard
Japanese Credit Bureau Credit Card
Master Card
Universal Air Travel Card
Visa
Used to specify a preference level for something that is or will be requested (e.g. a supplier of a service, a type of service, a form of payment, etc.).
Preference level that indicates request is unnacceptable for a specific criterion.
Preference level that indicates request is preferred for a specific criterion.
Preference level that indicates request is required for a specific criterion.
Preference level that indicates there is no preference.
To specify the type of action requested when more than one function could be handled by the message.
Identifies the specific type of special equipment. Refer to OTA Code List Equipment Type (EQP).
The number of doors on a vehicle. This may be an exact number or a range, i.e. 2-4.
Street direction of an address (e.g., N, E, S, NW, SW).
Describes the bank account used for financing travel (e.g., checking, savings, investment).
Type of contact in the context of use for the travel experience; such as permanent, temporary, affiliation,
travel arranger, etc.
This attribute may be used to provide a title for the formatted free text,
for example, Driving Directions. Each of the sub sections that are defined
to be a part of the formatted text would provide detailed information about
the subject identified by the title.
Candidate for removal, usage is not recommended. Deprecation Warning added in 2006A. Candidate for removal because the enumeration is misspelled. A replacement enumeration of the same value has been added with the correct spelling.
Remove in 2006B.
SpecialRequests : SpecialRequest
A collection of SpecialRequest objects. The collection of all special requests associated with any part of the reservation (the reservation in its entirety, one or more guests, or one or more room stays). Which special requests belong to which part is determined by each objects SpecialRequestRPHs collection.
Allows extensions
to be added to the OTA specification per trading partner agreement.
Refer to OTA Code List Travel Sector (TVS).
Code identifying the fee (e.g.,agency fee, municipality fee). Refer to OTA Code List Fee Tax Type (FTT).
May be used to give further detail on the code or to remove an obsolete item.
A text field used to communicate the context (or source of - ex Sabre, Galileo, Worldspan, Amadeus) the HotelReferenceGroup codes.
This request message is a subsequent request based on the previous message sent in this transaction.
Candidate for potential removal, usage is not recommended. Deprecation Warning added in 2006A.
Refer to OTA Code List Seat Preference (STP).
Refer to OTA Code List Phone Location Type (PLT).
'
GO
*/
--* Create the table
CREATE TABLE ResDataXml (
ResItemId int not null,
ResXml xml/*(OTA_ResNotifRQ_xsd)*/
CONSTRAINT [PK_ResItemId] PRIMARY KEY CLUSTERED
(
[ResItemId] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]
GO
--* Create the indexes
CREATE PRIMARY XML INDEX IX_ResXmlPrimary ON ResDataXml(ResXml)
CREATE XML INDEX IX_ResXmlPath ON ResDataXml(ResXml)
USING XML INDEX IX_ResXmlPrimary FOR PATH
CREATE XML INDEX IX_ResXmlValud ON ResDataXml(ResXml)
USING XML INDEX IX_ResXmlPrimary FOR VALUE
--* Insert some data
/*
INSERT INTO ResDataXml(ResItemId, ResXml)
SELECT ResItemId, XmlData FROM ResData
*/
INSERT INTO ResDataXml(ResItemId, ResXml)
SELECT 1,
' 'AS TmpXml
SELECT ResXml.exist('/OTA_HotelResNotifRQ/HotelReservations/HotelReservation')
FROM ResDataXml
SELECT ResXml.exist('/OTA_HotelResNotifRQ/HotelReservations/UniqueId[@Type="1"]')
FROM ResDataXml
.
April 13, 2009 at 8:42 pm
Could you attach it as a txt or zip file please? thanks.
[font="Times New Roman"]-- RBarryYoung[/font], [font="Times New Roman"] (302)375-0451[/font] blog: MovingSQL.com, Twitter: @RBarryYoung[font="Arial Black"]
Proactive Performance Solutions, Inc. [/font][font="Verdana"] "Performance is our middle name."[/font]
April 13, 2009 at 8:49 pm
Sure thing. Sorry. Didn't realize I could do that in this forum.
BTW, here's the error:
Msg 2260, Level 16, State 1, Line 18
XQuery [query()]: There is no element named 'OTA_HotelResNotifRQ'
Thx!
.
April 13, 2009 at 9:57 pm
Actually, I can make this question MUCH simpler. Can anybody demonstrate how to parse a few values out of this document. My attempts are missing the boat somewhere. Here's what I've got:
DECLARE @doc XML
SELECT @doc = ' '
SELECT @doc.value('/OTA_HotelResNotifRQ[1]/HotelReservations[1]/HotelReservation[1]/@CreateDateTime', 'datetime')
SELECT @doc.value('/OTA_HotelResNotifRQ[1]/HotelReservations[1]/HotelReservation[1]/@CreateDateTime', 'varchar(80)')
.
April 14, 2009 at 12:10 am
I've been at this all day, and I cannot get one query/value/exist to work against that fairly complicated xml presented in the last post. Does SS5 come up short? Can it not handle more complicated schemas? Surely my sorry little reservation xml can't be sending it crying back home to mama! 🙂
.
April 14, 2009 at 12:54 am
In the text file that you attached you are using a typed XML. In your XML schema collection and in the XML it self you specify which namespace should be used. When XQuery is used with typed XML that has a namespace, you should also specify the namespace in your query. If you don’t do that, you’ll get an error message. Bellow are 3 different ways to specify which namespace the XQuery should use (the name space in the examples is - http://schemas.adi.demo/UseNameSpace/Company):
--This example shows how to work with typed XML that has namespace.
declare @xml xml (UseNameSpace)
set @xml =
'
'
--The query has to parts that are separted
--by the ; sign. The first part is called prolog
--and it specifies which namespace should be used.
--You declare the namespace and give it a shortcut
--(In the exemple bellow the shourtcut is c).
--In the second part you use the shortcut (notice
--that I have to use it in each level in the Xpath expression)
select @xml.query('declare namespace c = "http://schemas.adi.demo/UseNameSpace/Company"; /c:root/c:Company')
--Another way is to define the namespace as default
--name sapce, then we don't need to specify the
--namespace in each level of the Xpath expression.
select @xml.query('declare default element namespace "http://schemas.adi.demo/UseNameSpace/Company"; /root/Company')
--You can use the with xmlnamespaces clause to define
--the name spaces that you will use. Notice that just like
--with regular CTE, if you use the with xmlnamspaces
--clause you have to put the ; sign at the end of the
--previouse statement.
;with xmlnamespaces (default 'http://schemas.adi.demo/UseNameSpace/Company')
select @xml.query('/root/Company')
Adi
--------------------------------------------------------------
To know how to ask questions and increase the chances of getting asnwers:
http://www.sqlservercentral.com/articles/Best+Practices/61537/
For better answers on performance questions, click on the following...
http://www.sqlservercentral.com/articles/SQLServerCentral/66909/
April 14, 2009 at 12:04 pm
That makes a lot of sense, I'm still missing something though. Let's forget about the typed xml for a minute. I don't understand why this returns NULL. The element I'm looking for is clearly in the xml. What am I missing? If I can figure this out, the rest will fall into place. Thanks!
DECLARE @doc XML
SELECT @doc = ' '
SELECT @doc.value('/OTA_HotelResNotifRQ[1]/EchoToken[1]', 'varchar(10)');
.
April 14, 2009 at 12:07 pm
Sorry, the xml did not make it into the post. Here's the attachment.
.
April 14, 2009 at 1:30 pm
Got it figured out. Thanks!! Definitlely got me over the hump. I've posted the solution in case anybody out there is interested, or if anybody else out there is working in the Travel industry and the OTA spec.
😀
.
Viewing 9 posts - 1 through 8 (of 8 total)
You must be logged in to reply to this topic. Login to reply