XQuery Over Document

  • 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

    .

  • 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]

  • 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!

    .

  • 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)')

    .

  • 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! 🙂

    .

  • 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/

  • 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)');

    .

  • Sorry, the xml did not make it into the post. Here's the attachment.

    .

  • 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