Just incase anyone else needs to do this, the following sql will find all orders with a status of complete between 1st of July and the 31st July 2015. It will return the CountryBvin and CountryName from the Shipping field in bvc_Order along with the Order Number and Order bvin
With
CTE AS (
SELECT o.[Bvin], o.[OrderNumber], cast (o.[ShippingAddress] AS xml) As XML, o.GrandTotal FROM [dbo].[bvc_Order] o
where o.[IsPlaced] = 1
and o.[TimeOfOrder] > '2015-06-30 23:59:59'
and o.[TimeOfOrder] <= '2015-07-31 23:59:59'
and o.StatusCode = '09D7305D-BD95-48d2-A025-16ADC827582A'
)
SELECT [Bvin], [OrderNumber], Shipping.addr.value('CountryBvin[1]','varchar(36)') as ShippingCountryBvin, Shipping.addr.value('CountryName[1]','nvarchar(max)') as ShippingCountryName
FROM CTE
CROSS APPLY xml.nodes('Address') Shipping(addr)
Order by [OrderNumber] asc
Edited by user Thursday, August 13, 2015 9:03:36 AM(UTC)
| Reason: typo