Easy query question

This one should be easy, but for some reason I can't get it.
I have a table with an XML column, containing the following:
<xs:ReservationChargeItem xmlns:xs="http://www.hoboo.com/ReservationSchema"
DropOffCode="" PickUpCode="" LastModified="0001-01-01T00:00:00Z">
<xs:Caption />
<xs:Description />
<xs:Period PickUp="2007-05-10T00:00:00Z" DropOff="2007-05-13T00:00:00Z"
Arrival="2007-05-10T00:00:00Z" Departure="2007-05-13T00:00:00Z" Nights="3"
Days="4" Weeks="1" />
<xs:Rateplan Caption="TN1|H" Description="" />
<xs:Roomtype Caption="A1A" Description="" />
<xs:Mealplan Caption="None" Description="" />
<xs:GuestIds>
<xs:GuestId>20070323163329yl0JSOpfGX</xs:GuestId>
<xs:GuestId>20070323163332GVKkSTnh98</xs:GuestId>
</xs:GuestIds>
<xs:DailyRates BillingType="PerRoom">
<xs:DailyRatesGuest>
<xs:Source Sell="107.9" Net="107.9" />
<xs:Target Sell="107.9" Net="107.9" />
</xs:DailyRatesGuest>
</xs:DailyRates>
<xs:DailyRates BillingType="PerRoom">
<xs:DailyRatesGuest>
<xs:Source Sell="124.5" Net="124.5" />
<xs:Target Sell="124.5" Net="124.5" />
</xs:DailyRatesGuest>
</xs:DailyRates>
<xs:DailyRates BillingType="PerRoom">
<xs:DailyRatesGuest>
<xs:Source Sell="132.8" Net="132.8" />
<xs:Target Sell="132.8" Net="132.8" />
</xs:DailyRatesGuest>
</xs:DailyRates>
<xs:Taxes Factor="0" ChargeType="Included" BillingType="PerRoom" Id="36"
ApplicableForCancellation="True">
<xs:Period PickUp="2007-05-10T00:00:00Z" DropOff="2007-05-13T00:00:00Z"
Arrival="2007-05-10T00:00:00Z" Departure="2007-05-13T00:00:00Z" Nights="3"
Days="4" Weeks="1" />
<xs:Caption>32.87</xs:Caption>
<xs:TotalSum>
<xs:Source Sell="32.87" Net="32.87" />
<xs:Target Sell="32.87" Net="32.87" />
</xs:TotalSum>
</xs:Taxes>
<xs:Products ChargeType="0" BillingType="GuestDesiredQuantity" Id="A1A -
TN1" NumberOfUnits="0">
<xs:Period From="0001-01-01T00:00:00Z" Until="0001-01-01T00:00:00Z"
PickUp="0001-01-01T00:00:00Z" DropOff="0001-01-02T00:00:00Z"
Arrival="0001-01-01T00:00:00Z" Departure="0001-01-02T00:00:00Z" Nights="1"
Days="2" Weeks="1" />
<xs:Caption>A1A - TN1</xs:Caption>
<xs:Description />
<xs:UnitRate>
<xs:Source Sell="0" Net="0" />
<xs:Target Sell="0" Net="0" />
</xs:UnitRate>
</xs:Products>
<xs:TotalSum>
<xs:Source Sell="398.07" Net="398.07" />
<xs:Target Sell="398.07" Net="398.07" />
</xs:TotalSum>
<xs:MultipleDriversFee ChargeType="0" />
<xs:PickUpFee />
<xs:DropOffFee />
<xs:DistanceLimit DistanceUnit="0">
<xs:FreeLimit Type="None" />
<xs:ExtraLimit Type="PerUnit" Distance="0">
<xs:Costs>
<xs:Source Sell="0" Net="0" />
<xs:Target Sell="0" Net="0" />
</xs:Costs>
</xs:ExtraLimit>
<xs:Information />
</xs:DistanceLimit>
</xs:ReservationChargeItem>
What I want is a select statement that gives me the values for net and sell
from the TotalSum/Source. Seems like it should be simple, but I'm missing
something somewhere. Thanks for any help you can give me.
Hello PK,
How's this:
use scratch
go
declare @.x xml
set @.x = '<?xml version="1.0" encoding="UTF-8"?>
<xs:ReservationChargeItem xmlns:xs="http://www.hoboo.com/ReservationSchema"
DropOffCode="" PickUpCode="" LastModified="0001-01-01T00:00:00Z">
<xs:Caption/>
<xs:Description/>
<xs:Period PickUp="2007-05-10T00:00:00Z" DropOff="2007-05-13T00:00:00Z"
Arrival="2007-05-10T00:00:00Z" Departure="2007-05-13T00:00:00Z" Nights="3"
Days="4" Weeks="1"/>
<xs:Rateplan Caption="TN1|H" Description=""/>
<xs:Roomtype Caption="A1A" Description=""/>
<xs:Mealplan Caption="None" Description=""/>
<xs:GuestIds>
<xs:GuestId>20070323163329yl0JSOpfGX</xs:GuestId>
<xs:GuestId>20070323163332GVKkSTnh98</xs:GuestId>
</xs:GuestIds>
<xs:DailyRates BillingType="PerRoom">
<xs:DailyRatesGuest>
<xs:Source Sell="107.9" Net="107.9"/>
<xs:Target Sell="107.9" Net="107.9"/>
</xs:DailyRatesGuest>
</xs:DailyRates>
<xs:DailyRates BillingType="PerRoom">
<xs:DailyRatesGuest>
<xs:Source Sell="124.5" Net="124.5"/>
<xs:Target Sell="124.5" Net="124.5"/>
</xs:DailyRatesGuest>
</xs:DailyRates>
<xs:DailyRates BillingType="PerRoom">
<xs:DailyRatesGuest>
<xs:Source Sell="132.8" Net="132.8"/>
<xs:Target Sell="132.8" Net="132.8"/>
</xs:DailyRatesGuest>
</xs:DailyRates>
<xs:Taxes Factor="0" ChargeType="Included" BillingType="PerRoom" Id="36"
ApplicableForCancellation="True">
<xs:Period PickUp="2007-05-10T00:00:00Z" DropOff="2007-05-13T00:00:00Z"
Arrival="2007-05-10T00:00:00Z" Departure="2007-05-13T00:00:00Z" Nights="3"
Days="4" Weeks="1"/>
<xs:Caption>32.87</xs:Caption>
<xs:TotalSum>
<xs:Source Sell="32.87" Net="32.87"/>
<xs:Target Sell="32.87" Net="32.87"/>
</xs:TotalSum>
</xs:Taxes>
<xs:Products ChargeType="0" BillingType="GuestDesiredQuantity" Id="A1A -
TN1" NumberOfUnits="0">
<xs:Period From="0001-01-01T00:00:00Z" Until="0001-01-01T00:00:00Z" PickUp="0001-01-01T00:00:00Z"
DropOff="0001-01-02T00:00:00Z" Arrival="0001-01-01T00:00:00Z" Departure="0001-01-02T00:00:00Z"
Nights="1" Days="2" Weeks="1"/>
<xs:Caption>A1A - TN1</xs:Caption>
<xs:Description/>
<xs:UnitRate>
<xs:Source Sell="0" Net="0"/>
<xs:Target Sell="0" Net="0"/>
</xs:UnitRate>
</xs:Products>
<xs:TotalSum>
<xs:Source Sell="398.07" Net="398.07"/>
<xs:Target Sell="398.07" Net="398.07"/>
</xs:TotalSum>
<xs:MultipleDriversFee ChargeType="0"/>
<xs:PickUpFee/>
<xs:DropOffFee/>
<xs:DistanceLimit DistanceUnit="0">
<xs:FreeLimit Type="None"/>
<xs:ExtraLimit Type="PerUnit" Distance="0">
<xs:Costs>
<xs:Source Sell="0" Net="0"/>
<xs:Target Sell="0" Net="0"/>
</xs:Costs>
</xs:ExtraLimit>
<xs:Information/>
</xs:DistanceLimit>
</xs:ReservationChargeItem>
'
selectt.c.value('data(@.Sell)','money') as Sell
, t.c.value('data(@.Net)','money') as Net
from @.x.nodes('declare namespace xs = "http://www.hoboo.com/ReservationSchema";
//xs:TotalSum/xs:Source') as t(c);
Thanks!
Kent Tegels
DevelopMentor
http://staff.develop.com/ktegels/
|||Fantastic! Thanks!
"Kent Tegels" wrote:

> Hello PK,
> How's this:
> use scratch
> go
> declare @.x xml
> set @.x = '<?xml version="1.0" encoding="UTF-8"?>
> <xs:ReservationChargeItem xmlns:xs="http://www.hoboo.com/ReservationSchema"
> DropOffCode="" PickUpCode="" LastModified="0001-01-01T00:00:00Z">
> <xs:Caption/>
> <xs:Description/>
> <xs:Period PickUp="2007-05-10T00:00:00Z" DropOff="2007-05-13T00:00:00Z"
> Arrival="2007-05-10T00:00:00Z" Departure="2007-05-13T00:00:00Z" Nights="3"
> Days="4" Weeks="1"/>
> <xs:Rateplan Caption="TN1|H" Description=""/>
> <xs:Roomtype Caption="A1A" Description=""/>
> <xs:Mealplan Caption="None" Description=""/>
> <xs:GuestIds>
> <xs:GuestId>20070323163329yl0JSOpfGX</xs:GuestId>
> <xs:GuestId>20070323163332GVKkSTnh98</xs:GuestId>
> </xs:GuestIds>
> <xs:DailyRates BillingType="PerRoom">
> <xs:DailyRatesGuest>
> <xs:Source Sell="107.9" Net="107.9"/>
> <xs:Target Sell="107.9" Net="107.9"/>
> </xs:DailyRatesGuest>
> </xs:DailyRates>
> <xs:DailyRates BillingType="PerRoom">
> <xs:DailyRatesGuest>
> <xs:Source Sell="124.5" Net="124.5"/>
> <xs:Target Sell="124.5" Net="124.5"/>
> </xs:DailyRatesGuest>
> </xs:DailyRates>
> <xs:DailyRates BillingType="PerRoom">
> <xs:DailyRatesGuest>
> <xs:Source Sell="132.8" Net="132.8"/>
> <xs:Target Sell="132.8" Net="132.8"/>
> </xs:DailyRatesGuest>
> </xs:DailyRates>
> <xs:Taxes Factor="0" ChargeType="Included" BillingType="PerRoom" Id="36"
> ApplicableForCancellation="True">
> <xs:Period PickUp="2007-05-10T00:00:00Z" DropOff="2007-05-13T00:00:00Z"
> Arrival="2007-05-10T00:00:00Z" Departure="2007-05-13T00:00:00Z" Nights="3"
> Days="4" Weeks="1"/>
> <xs:Caption>32.87</xs:Caption>
> <xs:TotalSum>
> <xs:Source Sell="32.87" Net="32.87"/>
> <xs:Target Sell="32.87" Net="32.87"/>
> </xs:TotalSum>
> </xs:Taxes>
> <xs:Products ChargeType="0" BillingType="GuestDesiredQuantity" Id="A1A -
> TN1" NumberOfUnits="0">
> <xs:Period From="0001-01-01T00:00:00Z" Until="0001-01-01T00:00:00Z" PickUp="0001-01-01T00:00:00Z"
> DropOff="0001-01-02T00:00:00Z" Arrival="0001-01-01T00:00:00Z" Departure="0001-01-02T00:00:00Z"
> Nights="1" Days="2" Weeks="1"/>
> <xs:Caption>A1A - TN1</xs:Caption>
> <xs:Description/>
> <xs:UnitRate>
> <xs:Source Sell="0" Net="0"/>
> <xs:Target Sell="0" Net="0"/>
> </xs:UnitRate>
> </xs:Products>
> <xs:TotalSum>
> <xs:Source Sell="398.07" Net="398.07"/>
> <xs:Target Sell="398.07" Net="398.07"/>
> </xs:TotalSum>
> <xs:MultipleDriversFee ChargeType="0"/>
> <xs:PickUpFee/>
> <xs:DropOffFee/>
> <xs:DistanceLimit DistanceUnit="0">
> <xs:FreeLimit Type="None"/>
> <xs:ExtraLimit Type="PerUnit" Distance="0">
> <xs:Costs>
> <xs:Source Sell="0" Net="0"/>
> <xs:Target Sell="0" Net="0"/>
> </xs:Costs>
> </xs:ExtraLimit>
> <xs:Information/>
> </xs:DistanceLimit>
> </xs:ReservationChargeItem>
> '
> selectt.c.value('data(@.Sell)','money') as Sell
> , t.c.value('data(@.Net)','money') as Net
> from @.x.nodes('declare namespace xs = "http://www.hoboo.com/ReservationSchema";
> //xs:TotalSum/xs:Source') as t(c);
> Thanks!
> Kent Tegels
> DevelopMentor
> http://staff.develop.com/ktegels/
>
>