Click here to Skip to main content
15,881,089 members
Search within:


XML Query Not Working
Please Sign up or sign in to vote.
4.00/5 (1 vote)
See more:
XML
SQL
Hello,

The following query (using an XML file as the source) is not working. the below-listed is the query as well as the XML file.

Thank you

SQL
SELECT x.y.value('(../../../ServiceType/text())[1]','varchar(50)') AS ServiceType
, x.y.value('(Amount/Currency/text())[1]','varchar(3)') as Currency
, x.y.value('(Description/text())[1]','varchar(255)') as [Description]
, x.y.value('(Amount/Amount/text())[1]','decimal (18, 2)') as [Amount]
, x.y.value('(../RateType/text())[1]','varchar(50)') as [RateType]
, @SessionID AS [SessionId]
FROM (SELECT @XML_Value AS XML_Value) AS T(x) Cross Apply x.nodes('RateReply/RateReplyDetails/RatedShipmentDetails/ShipmentRateDetail/Surcharges') as X(y)



---XML File
XML
<?xml version="1.0"?>

-<RateReply xmlns:SOAP-ENV="http://schemas.xmlsoap.org/soap/envelope/" xmlns="http://fedex.com/ws/rate/v9">

<HighestSeverity>NOTE</HighestSeverity>


-<Notifications>

<Severity>NOTE</Severity>

<Source>crs</Source>

<Code>886</Code>

<Message>Money Back Guarantee is not eligible for this pick up/delivery postal/zip code. </Message>

<LocalizedMessage>Money Back Guarantee is not eligible for this pick up/delivery postal/zip code. </LocalizedMessage>

</Notifications>


-<Notifications>

<Severity>NOTE</Severity>

<Source>crs</Source>

<Code>886</Code>

<Message>Money Back Guarantee is not eligible for this pick up/delivery postal/zip code. FDXG</Message>

<LocalizedMessage>Money Back Guarantee is not eligible for this pick up/delivery postal/zip code. FDXG</LocalizedMessage>


-<MessageParameters>

<Id>OPERATING_COMPANY</Id>

<Value>FDXG</Value>

</MessageParameters>

</Notifications>


-<Notifications>

<Severity>NOTE</Severity>

<Source>crs</Source>

<Code>819</Code>

<Message>The origin state/province code has been changed. </Message>

<LocalizedMessage>The origin state/province code has been changed. </LocalizedMessage>

</Notifications>


-<Notifications>

<Severity>NOTE</Severity>

<Source>crs</Source>

<Code>820</Code>

<Message>The destination state/province code has been changed. </Message>

<LocalizedMessage>The destination state/province code has been changed. </LocalizedMessage>

</Notifications>


-<TransactionDetail>

<CustomerTransactionId>WSVC RAS Example</CustomerTransactionId>

</TransactionDetail>


-<Version>

<ServiceId>crs</ServiceId>

<Major>9</Major>

<Intermediate>0</Intermediate>

<Minor>0</Minor>

</Version>


-<RateReplyDetails>

<ServiceType>INTERNATIONAL_PRIORITY</ServiceType>

<PackagingType>YOUR_PACKAGING</PackagingType>

<DeliveryStation>YOOB </DeliveryStation>

<DeliveryDayOfWeek>MON</DeliveryDayOfWeek>

<DeliveryTimestamp>2014-07-28T10:30:00</DeliveryTimestamp>


-<CommitDetails>

<CommodityName>DOCUMENTS</CommodityName>

<ServiceType>INTERNATIONAL_PRIORITY</ServiceType>

<CommitTimestamp>2014-07-28T10:30:00</CommitTimestamp>

<DayOfWeek>MON</DayOfWeek>

<DestinationServiceArea>AA</DestinationServiceArea>

<BrokerToDestinationDays>0</BrokerToDestinationDays>


-<CommitMessages>

<Code>108</Code>

<Message>WEEKEND DEL AVAILABLE. SH REQD WITH WEEKEND DELIVERY</Message>

</CommitMessages>


-<CommitMessages>

<Code>134</Code>

<Message>REQUEST COMPLETED</Message>

</CommitMessages>

<DeliveryMessages>10:30 A.M. IF NO CUSTOMS DELAY</DeliveryMessages>


-<DelayDetails>

<Date>2014-07-27</Date>

<DayOfWeek>SUN</DayOfWeek>

<Level>COUNTRY</Level>

<Point>DESTINATION</Point>

<Type>NO_SERVICE_AREA_DELIVERY</Type>

<Description>NO SVC AREA DELIVERY</Description>

</DelayDetails>

<DocumentContent>DOCUMENTS_ONLY</DocumentContent>

<RequiredDocuments>INTERNATIONAL_AIRWAY_BILL</RequiredDocuments>

</CommitDetails>

<DestinationAirportId>YYZ</DestinationAirportId>

<IneligibleForMoneyBackGuarantee>false</IneligibleForMoneyBackGuarantee>

<OriginServiceArea>A2</OriginServiceArea>

<DestinationServiceArea>A3</DestinationServiceArea>

<SignatureOption>SERVICE_DEFAULT</SignatureOption>

<ActualRateType>PAYOR_ACCOUNT_SHIPMENT</ActualRateType>


-<RatedShipmentDetails>


-<ShipmentRateDetail>

<RateType>PAYOR_ACCOUNT_SHIPMENT</RateType>

<RateScale>0000000</RateScale>

<RateZone>US001O</RateZone>

<PricingCode>ACTUAL</PricingCode>

<RatedWeightMethod>ACTUAL</RatedWeightMethod>


-<CurrencyExchangeRate>

<FromCurrency>USD</FromCurrency>

<IntoCurrency>USD</IntoCurrency>

<Rate>1.0</Rate>

</CurrencyExchangeRate>

<DimDivisor>0</DimDivisor>

<FuelSurchargePercent>16.5</FuelSurchargePercent>


-<TotalBillingWeight>

<Units>LB</Units>

<Value>15.0</Value>

</TotalBillingWeight>


-<TotalBaseCharge>

<Currency>USD</Currency>

<Amount>143.03</Amount>

</TotalBaseCharge>


-<TotalFreightDiscounts>

<Currency>USD</Currency>

<Amount>0.0</Amount>

</TotalFreightDiscounts>


-<TotalNetFreight>

<Currency>USD</Currency>

<Amount>143.03</Amount>

</TotalNetFreight>


-<TotalSurcharges>

<Currency>USD</Currency>

<Amount>23.6</Amount>

</TotalSurcharges>


-<TotalNetFedExCharge>

<Currency>USD</Currency>

<Amount>166.63</Amount>

</TotalNetFedExCharge>


-<TotalTaxes>

<Currency>USD</Currency>

<Amount>0.0</Amount>

</TotalTaxes>


-<TotalNetCharge>

<Currency>USD</Currency>

<Amount>166.63</Amount>

</TotalNetCharge>


-<TotalRebates>

<Currency>USD</Currency>

<Amount>0.0</Amount>

</TotalRebates>


-<Surcharges>

<SurchargeType>FUEL</SurchargeType>

<Description>Fuel</Description>


-<Amount>

<Currency>USD</Currency>

<Amount>23.6</Amount>

</Amount>

</Surcharges>

</ShipmentRateDetail>

</RatedShipmentDetails>


-<RatedShipmentDetails>


-<ShipmentRateDetail>

<RateType>RATED_ACCOUNT_SHIPMENT</RateType>

<RateScale>0000000</RateScale>

<RateZone>US001O</RateZone>

<PricingCode>ACTUAL</PricingCode>

<RatedWeightMethod>ACTUAL</RatedWeightMethod>


-<CurrencyExchangeRate>

<FromCurrency>USD</FromCurrency>

<IntoCurrency>USD</IntoCurrency>

<Rate>1.0</Rate>

</CurrencyExchangeRate>

<DimDivisor>0</DimDivisor>

<FuelSurchargePercent>16.5</FuelSurchargePercent>


-<TotalBillingWeight>

<Units>LB</Units>

<Value>15.0</Value>

</TotalBillingWeight>


-<TotalBaseCharge>

<Currency>USD</Currency>

<Amount>143.03</Amount>

</TotalBaseCharge>


-<TotalFreightDiscounts>

<Currency>USD</Currency>

<Amount>0.0</Amount>

</TotalFreightDiscounts>


-<TotalNetFreight>

<Currency>USD</Currency>

<Amount>143.03</Amount>

</TotalNetFreight>


-<TotalSurcharges>

<Currency>USD</Currency>

<Amount>23.6</Amount>

</TotalSurcharges>


-<TotalNetFedExCharge>

<Currency>USD</Currency>

<Amount>166.63</Amount>

</TotalNetFedExCharge>


-<TotalTaxes>

<Currency>USD</Currency>

<Amount>0.0</Amount>

</TotalTaxes>


-<TotalNetCharge>

<Currency>USD</Currency>

<Amount>166.63</Amount>

</TotalNetCharge>


-<TotalRebates>

<Currency>USD</Currency>

<Amount>0.0</Amount>

</TotalRebates>


-<Surcharges>

<SurchargeType>FUEL</SurchargeType>

<Description>Fuel</Description>


-<Amount>

<Currency>USD</Currency>

<Amount>23.6</Amount>

</Amount>

</Surcharges>

</ShipmentRateDetail>

</RatedShipmentDetails>

</RateReplyDetails>


-<RateReplyDetails>

<ServiceType>INTERNATIONAL_ECONOMY</ServiceType>

<PackagingType>YOUR_PACKAGING</PackagingType>

<DeliveryStation>YOOB </DeliveryStation>

<DeliveryDayOfWeek>TUE</DeliveryDayOfWeek>

<DeliveryTimestamp>2014-07-29T17:00:00</DeliveryTimestamp>


-<CommitDetails>

<CommodityName>DOCUMENTS</CommodityName>

<ServiceType>INTERNATIONAL_ECONOMY</ServiceType>

<CommitTimestamp>2014-07-29T17:00:00</CommitTimestamp>

<DayOfWeek>TUE</DayOfWeek>

<DestinationServiceArea>AA</DestinationServiceArea>

<BrokerToDestinationDays>0</BrokerToDestinationDays>


-<CommitMessages>

<Code>134</Code>

<Message>REQUEST COMPLETED</Message>

</CommitMessages>

<DeliveryMessages> 5:00 P.M. IF NO CUSTOMS DELAY</DeliveryMessages>

<DeliveryMessages> 8:00 P.M. IF RESIDENTIAL DELIVERY</DeliveryMessages>

<DocumentContent>DOCUMENTS_ONLY</DocumentContent>

<RequiredDocuments>INTERNATIONAL_AIRWAY_BILL</RequiredDocuments>

</CommitDetails>

<DestinationAirportId>YYZ</DestinationAirportId>

<IneligibleForMoneyBackGuarantee>false</IneligibleForMoneyBackGuarantee>

<OriginServiceArea>A2</OriginServiceArea>

<DestinationServiceArea>A3</DestinationServiceArea>

<SignatureOption>SERVICE_DEFAULT</SignatureOption>

<ActualRateType>PAYOR_ACCOUNT_SHIPMENT</ActualRateType>


-<RatedShipmentDetails>


-<ShipmentRateDetail>

<RateType>PAYOR_ACCOUNT_SHIPMENT</RateType>

<RateScale>0000000</RateScale>

<RateZone>US001O</RateZone>

<PricingCode>ACTUAL</PricingCode>

<RatedWeightMethod>ACTUAL</RatedWeightMethod>


-<CurrencyExchangeRate>

<FromCurrency>USD</FromCurrency>

<IntoCurrency>USD</IntoCurrency>

<Rate>1.0</Rate>

</CurrencyExchangeRate>

<DimDivisor>0</DimDivisor>

<FuelSurchargePercent>16.5</FuelSurchargePercent>


-<TotalBillingWeight>

<Units>LB</Units>

<Value>15.0</Value>

</TotalBillingWeight>


-<TotalBaseCharge>

<Currency>USD</Currency>

<Amount>121.19</Amount>

</TotalBaseCharge>


-<TotalFreightDiscounts>

<Currency>USD</Currency>

<Amount>0.0</Amount>

</TotalFreightDiscounts>


-<TotalNetFreight>

<Currency>USD</Currency>

<Amount>121.19</Amount>

</TotalNetFreight>


-<TotalSurcharges>

<Currency>USD</Currency>

<Amount>20.0</Amount>

</TotalSurcharges>


-<TotalNetFedExCharge>

<Currency>USD</Currency>

<Amount>141.19</Amount>

</TotalNetFedExCharge>


-<TotalTaxes>

<Currency>USD</Currency>

<Amount>0.0</Amount>

</TotalTaxes>


-<TotalNetCharge>

<Currency>USD</Currency>

<Amount>141.19</Amount>

</TotalNetCharge>


-<TotalRebates>

<Currency>USD</Currency>

<Amount>0.0</Amount>

</TotalRebates>


-<Surcharges>

<SurchargeType>FUEL</SurchargeType>

<Description>Fuel</Description>


-<Amount>

<Currency>USD</Currency>

<Amount>20.0</Amount>

</Amount>

</Surcharges>

</ShipmentRateDetail>

</RatedShipmentDetails>


-<RatedShipmentDetails>


-<ShipmentRateDetail>

<RateType>RATED_ACCOUNT_SHIPMENT</RateType>

<RateScale>0000000</RateScale>

<RateZone>US001O</RateZone>

<PricingCode>ACTUAL</PricingCode>

<RatedWeightMethod>ACTUAL</RatedWeightMethod>


-<CurrencyExchangeRate>

<FromCurrency>USD</FromCurrency>

<IntoCurrency>USD</IntoCurrency>

<Rate>1.0</Rate>

</CurrencyExchangeRate>

<DimDivisor>0</DimDivisor>

<FuelSurchargePercent>16.5</FuelSurchargePercent>


-<TotalBillingWeight>

<Units>LB</Units>

<Value>15.0</Value>

</TotalBillingWeight>


-<TotalBaseCharge>

<Currency>USD</Currency>

<Amount>121.19</Amount>

</TotalBaseCharge>


-<TotalFreightDiscounts>

<Currency>USD</Currency>

<Amount>0.0</Amount>

</TotalFreightDiscounts>


-<TotalNetFreight>

<Currency>USD</Currency>

<Amount>121.19</Amount>

</TotalNetFreight>


-<TotalSurcharges>

<Currency>USD</Currency>

<Amount>20.0</Amount>

</TotalSurcharges>


-<TotalNetFedExCharge>

<Currency>USD</Currency>

<Amount>141.19</Amount>

</TotalNetFedExCharge>


-<TotalTaxes>

<Currency>USD</Currency>

<Amount>0.0</Amount>

</TotalTaxes>


-<TotalNetCharge>

<Currency>USD</Currency>

<Amount>141.19</Amount>

</TotalNetCharge>


-<TotalRebates>

<Currency>USD</Currency>

<Amount>0.0</Amount>

</TotalRebates>


-<Surcharges>

<SurchargeType>FUEL</SurchargeType>

<Description>Fuel</Description>


-<Amount>

<Currency>USD</Currency>

<Amount>20.0</Amount>

</Amount>

</Surcharges>

</ShipmentRateDetail>

</RatedShipmentDetails>

</RateReplyDetails>


-<RateReplyDetails>

<ServiceType>FEDEX_GROUND</ServiceType>

<PackagingType>YOUR_PACKAGING</PackagingType>

<DeliveryStation>YOOB </DeliveryStation>


-<CommitDetails>

<ServiceType>FEDEX_GROUND</ServiceType>

<TransitTime>FIVE_DAYS</TransitTime>

<BrokerToDestinationDays>0</BrokerToDestinationDays>

</CommitDetails>

<DestinationAirportId>YYZ</DestinationAirportId>

<IneligibleForMoneyBackGuarantee>true</IneligibleForMoneyBackGuarantee>

<OriginServiceArea>A2</OriginServiceArea>

<DestinationServiceArea>A3</DestinationServiceArea>

<TransitTime>FIVE_DAYS</TransitTime>

<SignatureOption>SERVICE_DEFAULT</SignatureOption>

<ActualRateType>PAYOR_ACCOUNT_PACKAGE</ActualRateType>


-<RatedShipmentDetails>


-<ShipmentRateDetail>

<RateType>PAYOR_ACCOUNT_PACKAGE</RateType>

<RateZone>51</RateZone>

<RatedWeightMethod>ACTUAL</RatedWeightMethod>

<DimDivisor>0</DimDivisor>

<FuelSurchargePercent>7.0</FuelSurchargePercent>


-<TotalBillingWeight>

<Units>LB</Units>

<Value>15.0</Value>

</TotalBillingWeight>


-<TotalBaseCharge>

<Currency>USD</Currency>

<Amount>30.4</Amount>

</TotalBaseCharge>


-<TotalFreightDiscounts>

<Currency>USD</Currency>

<Amount>0.0</Amount>

</TotalFreightDiscounts>


-<TotalNetFreight>

<Currency>USD</Currency>

<Amount>30.4</Amount>

</TotalNetFreight>


-<TotalSurcharges>

<Currency>USD</Currency>

<Amount>2.13</Amount>

</TotalSurcharges>


-<TotalNetFedExCharge>

<Currency>USD</Currency>

<Amount>32.53</Amount>

</TotalNetFedExCharge>


-<TotalTaxes>

<Currency>USD</Currency>

<Amount>0.0</Amount>

</TotalTaxes>


-<TotalNetCharge>

<Currency>USD</Currency>

<Amount>32.53</Amount>

</TotalNetCharge>


-<TotalRebates>

<Currency>USD</Currency>

<Amount>0.0</Amount>

</TotalRebates>


-<Surcharges>

<SurchargeType>FUEL</SurchargeType>

<Level>PACKAGE</Level>

<Description>FedEx Ground Fuel</Description>


-<Amount>

<Currency>USD</Currency>

<Amount>2.13</Amount>

</Amount>

</Surcharges>

</ShipmentRateDetail>


-<RatedPackages>

<GroupNumber>0</GroupNumber>


-<PackageRateDetail>

<RateType>PAYOR_ACCOUNT_PACKAGE</RateType>

<RatedWeightMethod>ACTUAL</RatedWeightMethod>


-<BillingWeight>

<Units>LB</Units>

<Value>15.0</Value>

</BillingWeight>


-<BaseCharge>

<Currency>USD</Currency>

<Amount>30.4</Amount>

</BaseCharge>


-<TotalFreightDiscounts>

<Currency>USD</Currency>

<Amount>0.0</Amount>

</TotalFreightDiscounts>


-<NetFreight>

<Currency>USD</Currency>

<Amount>30.4</Amount>

</NetFreight>


-<TotalSurcharges>

<Currency>USD</Currency>

<Amount>2.13</Amount>

</TotalSurcharges>


-<NetFedExCharge>

<Currency>USD</Currency>

<Amount>32.53</Amount>

</NetFedExCharge>


-<TotalTaxes>

<Currency>USD</Currency>

<Amount>0.0</Amount>

</TotalTaxes>


-<NetCharge>

<Currency>USD</Currency>

<Amount>32.53</Amount>

</NetCharge>


-<TotalRebates>

<Currency>USD</Currency>

<Amount>0.0</Amount>

</TotalRebates>


-<Surcharges>

<SurchargeType>FUEL</SurchargeType>

<Level>PACKAGE</Level>

<Description>FedEx Ground Fuel</Description>


-<Amount>

<Currency>USD</Currency>

<Amount>2.13</Amount>

</Amount>

</Surcharges>

</PackageRateDetail>

</RatedPackages>

</RatedShipmentDetails>

</RateReplyDetails>

</RateReply>
Posted
Add a Solution
Comments
PIEBALDconsult 25-Jul-14 18:53pm    
Some indication of the expected result and actual result would help. And I don't think you should need to include so much of the XML.

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

  Print Answers RSS
Top Experts
Last 24hrsThis month

Advertise
Privacy
Cookies
Terms of Use
Last Updated 25 Jul 2014
Layout: fixed | fluid
Copyright © CodeProject, 1999-2024
All Rights Reserved.

Web01 2.8:2024-04-02:1

CodeProject, 20 Bay Street, 11th Floor Toronto, Ontario, Canada M5J 2N8 +1 (416) 849-8900