Post reply

XQuery with several different node criteria

  • February 1, 2008 at 12:44 pm

    #186181

    I have an XML datatype field that management wants a "simple report" from. There are seven different fields they want, each with their own set of XQuery criteria.

    I can write the SELECT statements for each field, but how do I get them to show up in normal columnar order? Meaning[Field2] [Field2] [Field3]

    F1data F2data F3data

    At the moment, with the SELECT statements one right after the other, I'm getting this:[Field1]

    F1data

    [Field2]

    F2data

    [Field3]

    F3data

    Any help would be greatly appreciated!

    Thanks!

    -JR

  • February 1, 2008 at 1:07 pm

    #774421

    Can you provide some more detail here. Sample XML, table structure, expected output etc.

    ____________________________________________________

    Deja View - The strange feeling that somewhere, sometime you've optimised this query before

    How to get the best help on a forum

    http://www.sqlservercentral.com/articles/Best+Practices/61537

  • February 1, 2008 at 1:43 pm

    #774441

    Sure. Here are the contents of one of the XML fields, with data changed only to protect company property. And no, it doesn't really have BEG- and -END in there. I could not figure how to get the board to show the gt and lt characters.

    Things that we need to search for are:

    * TransmissionType = QUOTEREQUEST

    * TransmissionDate between supplied dates

    * Presence of full BEG-ForeignRef-END tag (in example below, only BEG-Foreign Ref /-END is shown, so this does NOT have the full tag

    * TransportMode = AB|CD

    * BillToID = 987654

    * Presence of full BEG-ResponseError-END tag

    BEG-Transmission-END

    BEG-TransmissionHeader-END

    BEG-TransmissionType-ENDQUOTEREQUESTBEG-/TransmissionType-END

    BEG-VersionID-END1.0BEG-/VersionID-END

    BEG-SenderID-END125BEG-/SenderID-END

    BEG-ReceiverID-END001BEG-/ReceiverID-END

    BEG-Password-ENDpasswordBEG-/Password-END

    BEG-TransmissionID-END1201306909617BEG-/TransmissionID-END

    BEG-TransmissionDate-END2008-01-25T19:21:00BEG-/TransmissionDate-END

    BEG-/TransmissionHeader-END

    BEG-Request-END

    BEG-RequestHeader-END

    BEG-ForeignRef /-END

    BEG-KnownShipper-ENDfalseBEG-/KnownShipper-END

    BEG-TransportMode-ENDCDBEG-/TransportMode-END

    BEG-/RequestHeader-END

    BEG-BillTo-END

    BEG-BillToID-END987654BEG-/BillToID-END

    BEG-/BillTo-END

    BEG-Requester-END

    BEG-Phone-END888-888-1234BEG-/Phone-END

    BEG-Email-ENDaddy@company.comBEG-/Email-END

    BEG-ContactName-ENDAddy UserBEG-/ContactName-END

    BEG-/Requester-END

    BEG-ShipAttributes-END

    BEG-HazMat-ENDfalseBEG-/HazMat-END

    BEG-HeaviestPiece-END500BEG-/HeaviestPiece-END

    BEG-TempCtrl-ENDfalseBEG-/TempCtrl-END

    BEG-FreightStackable-ENDfalseBEG-/FreightStackable-END

    BEG-/ShipAttributes-END

    BEG-SendFrom-END

    BEG-ReadyDate-END2008-01-25T00:00:00BEG-/ReadyDate-END

    BEG-Location-END

    BEG-CompanyName /-END

    BEG-AddressLine1 /-END

    BEG-AddressLine2 /-END

    BEG-City-ENDBEVERLY HILLSBEG-/City-END

    BEG-StateProv-ENDCABEG-/StateProv-END

    BEG-PostalCode-END90210BEG-/PostalCode-END

    BEG-CountryCode-ENDUSABEG-/CountryCode-END

    BEG-LocationType-ENDBUSINESSBEG-/LocationType-END

    BEG-/Location-END

    BEG-/SendFrom-END

    BEG-SendTo-END

    BEG-Location-END

    BEG-CompanyName /-END

    BEG-AddressLine1 /-END

    BEG-AddressLine2 /-END

    BEG-City-ENDCHICAGOBEG-/City-END

    BEG-StateProv-ENDILBEG-/StateProv-END

    BEG-PostalCode-END60606BEG-/PostalCode-END

    BEG-CountryCode-ENDUSABEG-/CountryCode-END

    BEG-LocationType-ENDBUSINESSBEG-/LocationType-END

    BEG-/Location-END

    BEG-/SendTo-END

    BEG-ShipUnitDetail-END

    BEG-ShipUnitCount-END1BEG-/ShipUnitCount-END

    BEG-PackageType-ENDPKGBEG-/PackageType-END

    BEG-Commodity /-END

    BEG-ActualWeight-END500BEG-/ActualWeight-END

    BEG-DimLength-END50.0BEG-/DimLength-END

    BEG-DimWidth-END50.0BEG-/DimWidth-END

    BEG-DimHeight-END50.0BEG-/DimHeight-END

    BEG-/ShipUnitDetail-END

    BEG-/Request-END

    BEG-Response-END

    BEG-ForeignRef /-END

    BEG-Product-END

    BEG-Guaranteed-ENDFalseBEG-/Guaranteed-END

    BEG-SendFrom-END

    BEG-ReadyDate-END2008-01-25T00:00:00BEG-/ReadyDate-END

    BEG-ByDate-END2008-01-25T00:00:00BEG-/ByDate-END

    BEG-Location-END

    BEG-City-ENDBEVERLY HILLSBEG-/City-END

    BEG-StateProv-ENDCABEG-/StateProv-END

    BEG-PostalCode-END90209BEG-/PostalCode-END

    BEG-CompanyName /-END

    BEG-Address1 /-END

    BEG-Address2 /-END

    BEG-Phone /-END

    BEG-Fax /-END

    BEG-Email /-END

    BEG-CountryCode-ENDUSABEG-/CountryCode-END

    BEG-LocationType-ENDBUSINESSBEG-/LocationType-END

    BEG-/Location-END

    BEG-/SendFrom-END

    BEG-SendTo-END

    BEG-ByDate-END2008-01-26T11:30:07BEG-/ByDate-END

    BEG-Location-END

    BEG-City-ENDCHICAGOBEG-/City-END

    BEG-StateProv-ENDILBEG-/StateProv-END

    BEG-PostalCode-END60607BEG-/PostalCode-END

    BEG-CompanyName /-END

    BEG-Address1 /-END

    BEG-Address2 /-END

    BEG-Phone /-END

    BEG-Fax /-END

    BEG-Email /-END

    BEG-CountryCode-ENDUSABEG-/CountryCode-END

    BEG-LocationType-ENDBUSINESSBEG-/LocationType-END

    BEG-/Location-END

    BEG-/SendTo-END

    BEG-Miles-END2054BEG-/Miles-END

    BEG-TransportType-ENDBBEG-/TransportType-END

    BEG-TotalSellPrice-END9,999.99BEG-/TotalSellPrice-END

    BEG-/Product-END

    BEG-QuoteNo-ENDX123456BEG-/QuoteNo-END

    BEG-/Response-END

    BEG-/Transmission-END

  • February 2, 2008 at 2:06 pm

    #774551

    See if this helps

    declare @x xml

    set @x='

    ... your xml

    '

    select r.query('.')

    from @x.nodes('/Transmission') as x(r)

    where r.exist('(TransmissionHeader/TransmissionType)[text()="QUOTEREQUEST"]')=1

    and r.value('(TransmissionHeader/TransmissionDate)[1]','datetime') between '20080101' and '20081231'

    and r.exist('(Request/RequestHeader/ForeignRef/text())[1]')=1

    and r.value('(Request/RequestHeader/TransportMode)[1]','char(2)') in ('AB','CD')

    and r.exist('(Request/BillTo/BillToID)[text()="987654"]') = 1

    and r.exist('(Request/ResponseError/text())[1]')=1

    ____________________________________________________

    Deja View - The strange feeling that somewhere, sometime you've optimised this query before

    How to get the best help on a forum

    http://www.sqlservercentral.com/articles/Best+Practices/61537

Viewing 4 posts - 1 through 3 (of 3 total)

You must be logged in to reply to this topic. Login to reply