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

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" Ws="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" Ws="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" Ws="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" Ws="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" Ws="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="000
1-01-02T00:00:00Z"
Nights="1" Days="2" Ws="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>
'
select t.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/ReservationSche
ma";
//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" Ws="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" Ws="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" Pick
Up="0001-01-01T00:00:00Z"
> DropOff="0001-01-02T00:00:00Z" Arrival="0001-01-01T00:00:00Z" Departure="0
001-01-02T00:00:00Z"
> Nights="1" Days="2" Ws="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>
> '
> select t.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/ReservationSc
hema";
> //xs:TotalSum/xs:Source') as t(c);
> Thanks!
> Kent Tegels
> DevelopMentor
> http://staff.develop.com/ktegels/
>
>

Easiest way to get a value in af file into a variable ?

I get a file with some key information delivered to an ftp destination each day along with some files containing rawdata.

The file is a csv file containing some short description of what is being delivered.

Numrows;pulltime;sourceinfo

25302524;25-01-2006;dssrv34

So the file has columndescription and 1 row with some information.

My question is, what is the easiest way to get those 3 informations into 3 variables ?

How about a Flat File Adapter into a script component in which you can load the values into the variables.

-Jamie

|||

Or you could do this: http://blogs.conchango.com/jamiethomson/archive/2005/06/15/1693.aspx

(I forgot I'd done this before)

-Jamie

|||Personally, I would create a script task that takes in a filespec variable and writes to three variables. Like so.

Dim strFileSpec as String = cstr(Dts.Variables("MyFileSpec").Value)

Dim sr As System.IO.StreamReader

Dim strVals() As String

' Check if file exists

If System.IO.File.Exists(strFileSpec) Then

' Open the stream reader

sr = New System.IO.StreamReader(strFileSpec)

' Check if not at end of stream

' Split the first line at the semicolons and put in string array

If Not sr.EndOfStream Then

strVals = sr.ReadLine.Split(";")

End If

' Close the stream reader

sr.Close

' Check if there are three variables,

' If so right to output variables

If strVals.GetLength = 3 Then

Dts.Variables("MyVar1").Value = strVals(0)

Dts.Variables("MyVar2").Value = strVals(1)

Dts.Variables("MyVar3").Value = strVals(2)

End If

End If

Larry Pope

Dynamically delete a row from report

Hi,
I have a report containing some values. Surprise? :)
I want to delete rows containing values 0 and null from the report. I need
to include those rows to the sql query, but don't wan't them to be seen in
that particular table. Can the dynamic deletion of a row be done?
Thanks for helping the noobie!No problem anymore, I think.
I don't have to delete the row, instead I just use the filter in the table
to exclude the values I don't want to be shown...

dynamically creating a select statement

I have a stored procedure in my database which will be used to search for records matching given criteria, such as within a date range or containing a keyword. The procedure stub is looking like this at the moment:

ALTER PROCEDURE [dbo].[search]
@.file_id int,
@.title_includes varchar(50),
@.notes_includes varchar(50),
@.updated_after datetime,
@.updated_before datetime,
@.deleted_after datetime,
@.deleted_before datetime,
@.size_bigger_than int,
@.size_smaller_than int
AS
BEGIN
-- SET NOCOUNT ON added to prevent extra result sets from
-- interfering with SELECT statements.
SET NOCOUNT ON;

-- Insert statements for procedure here
--SELECT <@.Param1, sysname, @.p1>, <@.Param2, sysname, @.p2>
END

Ideally I would like criteria to only be used in the select statement if the value passed in is not null, but as far as i know it is not possible to place and if-then condition in the middle of a select statement to check if the parameter is null? Does anyone know of an efficient way to achieve this functionality? Any help will be greatly appreciated, thank you.

There's two approaches that I can think of that might help you.

Firstly, it's entirely valid to include criteria such as

WHERE (file_id = @.file_id or @.file_id is null)
AND (title_includes = @.title_includes or @.title_includes is null)
AND ...

in the stored procedure.

However, if you mean that you only want to see the file_id column in the results if the passed parameter @.file_id is not null, then you need to approach it differently, as follows:

declare @.select nvarchar(max)
declare @.where nvarchar(max)
declare @.selectsep nvarchar(5)
declare @.wheresep nvarchar(5)

set @.select = N''
set @.selectsep = N''
set @.where = N''
set @.wheresep = N''

if (@.file_id is not null)
begin
set @.select = @.select + @.selectsep + N'file_id'
set @.where = @.where + @.wheresep + N'file_id = @.file_id'
set @.selectsep = N','
set @.wheresep = N' and '
end

if (@.title_includes is not null)
begin
set @.select = @.select + @.selectsep + N'title_includes'
set @.where = @.where + @.wheresep + N'title_includes = @.title_includes'
set @.selectsep = N','
set @.wheresep = N' and '
end

... and so on for the other parameters ...

set @.select = N'SELECT <list of fields you always want to include>, ' + @.select + N'FROM <from clause>'
if (len(@.where) > 0)
begin
set @.select = @.select + N' WHERE ' + @.where
end

exec dbo.sp_executesql @.select
, N' @.file_id int, @.title_includes varchar(50), @.notes_includes varchar(50), @.updated_after datetime, @.updated_before datetime, @.deleted_after datetime, @.deleted_before datetime, @.size_bigger_than int, @.size_smaller_than int'
, @.file_id = @.file_id
, @.title_includes = @.title_includes
, @.notes_includes = @.notes_includes
, @.updated_after = @.updated_after
, @.updated_before = @.updated_before
, @.deleted_after = @.deleted_after
, @.deleted_before = @.deleted_before
, @.size_bigger_than = @.size_bigger_than
, @.size_smaller_than = @.size_smaller_than

Naturally, as you build up the @.select and @.where variables, you can include the usual range of operators (like, <, >, etc).

Let me know if you need any further assistance with this.

Iain

|||

You can try this:

set ANSI_NULLS ON

set QUOTED_IDENTIFIER ON

go

ALTER PROCEDURE [dbo].[SearchCurrency]

@.currencycode nchar(5) = null,

@.currencyname nchar(25) = null

as

begin

declare @.stmt nvarchar(max)

set @.stmt = 'SELECT currencycode, currencyname

FROM currencies where 1=1 '

IF @.currencycode IS NOT NULL

set @.stmt = @.stmt + ' AND currencycode = '''+ @.currencycode + ''''

IF @.currencyname IS NOT NULL

set @.stmt = @.stmt + ' AND currencyname = ''' + @.currencyname +''''

exec(@.stmt)

end

|||

I highly recommend reading Erland's article on Dynamic SQL before going down this path. It may work well for you, but you should be properly informed about the pitfalls.

See:

Dynamic SQL - The Curse and Blessings of Dynamic SQL
http://www.sommarskog.se/dynamic_sql.html

Dynamic table name in sp?

Hi,

I need to create a stored procedure that accepts a string parameter containing a table name and truncates the table if it exists and creates it if it doesn't. Is there a way to do this without creating the whole sp in a string and then executing it?

For example, I can do this:

DECLARE @.ThisQuery VARCHAR(8000)

SET @.ThisQuery = 'Create Table ' + @.TableName <--this is the parameter

SET @.ThisQuery = @.ThisQuery + '...'

EXEC (@.ThisQuery)

Is there a better way that does not involve creating a dynamic query like this?

Thanks

I would convert the varchar(8000) to varchar(max); otherwise I would be converned that you might overflow your variable. Yes, this looks like it will work. As an aside I would personally try to avoid this type of generic operation.|||

Using Dynamic SQL for schema change issues is extremely dangerous. First, there would have to be a high level of permission to accomplish the task, and second, it could leave your server open to SQL Injection attacks.

You would be 'safer' using [ sp_executesql() ], and I recommend using unicode, so [ nvarchar(max) ] would be in order.

You may find this article by Erland worth the time to read.

Dynamic SQL - The Curse and Blessings of Dynamic SQL
http://www.sommarskog.se/dynamic_sql.html

|||

Thank you for this help. Yes, I understand it's dangerous, that's why I'm looking for another way to do it! The article seems great, but it is very long and verbose and after looking through it for a while now and I'm still not sure if it can be done and how. Is there possibly just some syntax someone can please give me that cuts through all this? I don't really need to understand it, I just need to know how to do it.

Many thanks.

|||

Well, it appears to me that with generic way that you are trying to do this that in the long run you are still going to have to have a script for each table create / truncate anyway. For me, I would just keep standard scripts for each table -- in fact, this is exactly what I do. I keep them in either Source Safe, Clear Case or ERWin

|||DECLARE @.ThisQuery VARCHAR(8000)
declare @.TableName sysname
set @.TableName = 'MyTab'
SET @.ThisQuery = 'if object_ID('''+@.TableName +''') is null '+ CHAR(13)+
'Create Table ' + @.TableName + '([Dummy] varchar(50))'
EXEC (@.ThisQuery)

|||You realize, I hope, that TRUNCATE TABLE will FAIL if there are PK-FK relationships.|||

>> Well, it appears to me that with generic way that you are trying to do this that in the long run you are still going to have to have a script for each table create / truncate anyway. For me, I would just keep standard scripts for each table -- in fact, this is exactly what I do. I keep them in either Source Safe, Clear Case or ERWin<<

100% agree with you here. Building tables in a stored procedure is generally a sign of using SQL in a non-preferred manner. We haven't been given any insight into the problem trying to be solved, but the "dummy" column makes it seem probably worse than it might actually be.

|||

I have to say, point well taken! After re-thinking my approach, I came up with a better way to do things that does not require a dynamic table name. Thanks.

Dynamic Table Name creation...

Hi there,

I am trying to generate tables names on the fly depending on another table. So i am creating a local variable containing the table names as required. I am storing the tables in a local variable called

@.TABLENAME VARCHAR(16)

and when i say SELECT * FROM @.TABLENAME

it is giving me an error and I think I cannot declare @.TABLENAME as a table variable because I do not want to create a temp table of sorts.

I hope I am clear.

thanks,

Murthy here

can you post the error generated by SQL server

and the code for generation of tables names , I am curious to see how we can achieve it :)

|||

Are you looking for something like this:

Declare @.tempTable table (
TableName varchar(16)
)

Insert into @.tempTable
Select 'Apples'
union
Select 'Pears'

Select * from @.tempTable

Results:
TableName
------
Apples
Pears

|||

Hi there,

I am posting a portion of my code:

SET @.TABLENAME = 'FISCURRENT.dbo.DC67TRANSCYR' + '0' + CAST((CAST((SUBSTRING(@.FISCALYEAR1,3,2)) AS INT) - 1) AS VARCHAR) + SUBSTRING(@.FISCALYEAR1,3,2)

SELECT DESCRIPTION, TRANSTYPE , BE, CATEGORY, FID , OCA, '67' + ORGL2L5 , EO, AMOUNT, GL, TRANSDATE, MACHINEDATE, 'N' AS BMS, THEYEAR
FROM @.TABLENAME
WHERE ((TRANSTYPE = '20') AND (GL = '92200')) OR
((TRANSTYPE = '21') AND (GL IN ('91100', '92100'))) OR
((TRANSTYPE = '22') AND (GL IN ('13100', '12200'))) AND (BE IN (SELECT DISTINCT BE FROM REF_BUDGETENTITY WHERE FISCALYEAR = @.FISCALYEAR))
And the error message is :

Must declare the table variable "@.TABLENAME".

thanks,

Murthy here

|||

Hi there,

I do not have to create any temp tables. Actually the tables already exist with sql server. based on 1 field from 1 table I have to select data from 1 or more different tables.

The select statement has to different for different tables so I am generating the table names which already exist on the fly.

Hope I am clear.

thanks,

Murthy here

|||

I think you will need to use sp_execsql to execute the dynamic sql you build that way

Keep in mind that many people discourage the use of dynamic sql because of security and performance

1. SQL injection attacks

2. Having to compile the statement every time

3. YOu need to grant permissions on the underlying tables, not on the proc

|||

If you are trying to do something like this:

Declare @.table varchar(20)
set @.table = Northwind.dbo.Products

Select * from @.table

You can't. It's looking for a table afterfromso @.table would have to be a table variable.

To do what you need, asdbland07666 mentioned, you need to use dynamic sql. Caveat Emptor. Here is a link that might help you:

http://www.nigelrivett.net/SQLTsql/TableNameAsVariable.html