declare @p XML

set @p = '
<ContractEntity xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xmlns:xsd="http://www.w3.org/2001/XMLSchema">
	<ContractHeader WasAmended="false" CustomerID="1800001917" ItemCount="1" BasketName="My Basket" CustomerRefNumber="16216966" ContractID="3967" CreatedDate="2009-05-21T08:35:45.1570035+01:00" ModifiedDateTime="2009-05-21T08:35:45.3973899+01:00" StoreId="2654">
		<PromotionIdentifiers />
		<Rewards />
		<ContractStatus>Pending</ContractStatus>
		<CustomerDetails>
			<ClubcardNumber>18000019176</ClubcardNumber>
			<CustomerId>1800001917</CustomerId>
			<CustomerType>PAH</CustomerType>
			<Email>angel@angel.com</Email>
		</CustomerDetails>
		<DeliveryAddress BuildingNameNumber="9" GridReference="0524202112" BuildingType="NotDefined" LegacyAddressString="#9 Chambers Grove,Welwyn Garden City" Nickname="BothBagOption" PafValidated="false" PostalTown="Welwyn Garden City" Postcode="AL74FG" StreetName="Chambers Grove" />
		<PaymentBasketEntity>
			<Coupon />
			<GiftCard />
			<PaymentCardDetails>
				<PaymentCardDetailsEntity AuthorisationCode="12345" CardAuthentAtsd="0" CardAuthentCavv="CAACA0mDkicQI0lARIOSAAAAAAA=" CardAuthentEci="06" CardAuthentPosem="0" CardAuthentXid="itqxO5dgak25DlMiHGOL4QEBAQE=" CardType="VI" StartDate="0101" CardholderName="Sam M" CardNumber="KZKZLZLZIZIZJZJZ" ExpiryDate="1212" IssueNumber="">
					<BillingAddress BuildingNameNumber="3" BuildingType="NotDefined" LegacyAddressString="#3 Pensilver Close,Barnet" Nickname="ThaAn" PafValidated="false" PostalTown="Barnet" Postcode="EN49BE" StreetName="Pensilver Close" />
				</PaymentCardDetailsEntity>
			</PaymentCardDetails>
			<Voucher />
			<PaymentSummary TotalGreenClubCardPoints="20" DeliveryCharge="5.9900" GuidePrice="0.4980" PromotionalClubCardPoints="0" StaffDiscount="0" StandardClubCardPoints="0" TotalClubCardPoints="20" TotalCoupons="0" TotalGiftCards="0" TotalVouchers="0" TotalSavings="0" />
		</PaymentBasketEntity>
		<DeliverySlot EndDateTime="2009-05-24T12:00:00" DeliverySlotId="26542009052410000020090524120000" GreenClubcardPoints="20" IsGreenDelivery="true" StartDateTime="2009-05-24T10:00:00" CutoffDateTime="2009-05-24T04:14:00">
			<IsBaglessDelivery>true</IsBaglessDelivery>
		</DeliverySlot>
		<Recipient Email="angel@angel.com" Forename="Sam" Initials="M " PhoneDay="01234567890" PhoneEvening="01234567890" PhoneMobile="" Surname="An" Title="Mr " />
			<DeliveryInstructions>
				<Instructions />
			</DeliveryInstructions>
	</ContractHeader>
	<GroceryBasketItemEntities>
		<GroceryBasketItemEntity ProductMaxWeight="0.00" ProductInStoreTotalPrice="0.4980" ProductAgerageWeight="0.00" LookForThisSection="" PickNote="" ChoiceQuantity="1" ChoiceWeight="0.00" AddedDateTime="2009-05-21T08:33:44.097" LastUpdated="0001-01-01T00:00:00" IsUntrusted="false" BaseProductId="64122416" ProductInStorePrice="0.4980" ProductId="264766639" ProductInStoreQuantity="1.00">
			<ProductType>Single</ProductType>
			<MeasureType>Unit</MeasureType>
			<UnitOfSale>Item</UnitOfSale>
			<SubstitutionOption>FindSuitableAlternative</SubstitutionOption>
			<ContractLineItemPromotion />
		</GroceryBasketItemEntity>
	</GroceryBasketItemEntities>
</ContractEntity>
'

declare @custid varchar(20)

set @custid = '1800001917'

SELECT		d.value('@CustomerID', 'VARCHAR(10)') AS CustomerID,
		d.value('@WasAmended', 'VARCHAR(5)') AS WasAmended,
		w.value('@LegacyAddressString', 'VARCHAR(200)') AS LegacyAddressString,
		q.value('@DeliverySlotId', 'VARCHAR(200)') AS DeliverySlotId
FROM		@p.nodes('/ContractEntity/ContractHeader') AS ch(d)
CROSS APPLY	d.nodes('DeliveryAddress') AS da(w)
CROSS APPLY	d.nodes('DeliverySlot') AS ds(q)
WHERE		d.exist('.[@CustomerID = sql:variable("@custid")]') = 1