SELECT c.CustomerID, c.PersonID AS BusinessEntityID, p.FirstName AS Name1, p.LastName AS Name2, ea.EmailAddress, ad.City, ad.PostalCode,
sp.Name AS StateProvince, sp.StateProvinceCode, ad.SpatialLocation.STDistance(@GeoLocation) / 1609.344 AS DistanceinMiles, ad.SpatialLocation,
p.Demographics.value('declare namespace awns="http://schemas.microsoft.com/sqlserver/2004/07/adventure-works/IndividualSurvey"; (awns:IndividualSurvey/awns:NumberCarsOwned) [1]',
'int') AS NumberCarsOwned,
p.Demographics.value('declare namespace awns="http://schemas.microsoft.com/sqlserver/2004/07/adventure-works/IndividualSurvey"; (awns:IndividualSurvey/awns:CommuteDistance) [1]',
'varchar(30)') AS CommuteDistance
FROM Sales.Customer AS c INNER JOIN
Person.Person AS p ON p.BusinessEntityID = c.PersonID INNER JOIN
Person.BusinessEntityAddress AS a ON a.BusinessEntityID = p.BusinessEntityID INNER JOIN
Person.AddressType AS t ON a.AddressTypeID = t.AddressTypeID INNER JOIN
Person.Address AS ad ON ad.AddressID = a.AddressID INNER JOIN
Person.EmailAddress AS ea ON ea.BusinessEntityID = p.BusinessEntityID INNER JOIN
Person.StateProvince AS sp ON sp.StateProvinceID = ad.StateProvinceID
WHERE (c.StoreID IS NULL) AND (t.Name = N'Home') AND (sp.CountryRegionCode = N'US') AND (ad.SpatialLocation.STDistance(@GeoLocation) / 1609.344 < @Radius) |