In my code below...how do I set the entire output as a variable and do a select on it?
DECLARE @title XML
SET @title = N'<?xml version="1.0" encoding="UTF-16" ?>'
DECLARE @link XML
SET @link = N'<?xml version = "1.0" encoding = "UTF-16"?><link>http://www.mydomain.com</link>'
DECLARE @description XML
SET @description = N'<?xml version = "1.0" encoding = "UTF-16"?><description>Current Real Estate Listings from My Domain Properties</description>'
DECLARE @lastBuildDate XML
SET @lastBuildDate = N'<?xml version = "1.0" encoding = "UTF-16"?><feedPublishDate>' + CONVERT (Varchar(10), getdate(), 101) + '</feedPublishDate>'
DECLARE @language XML
SET @language = N'<?xml version = "1.0" encoding = "UTF-16"?><language>en-us</language>'
--Non-Recurring header Section
--Recurring Properties Section
(select [listing_id] as 'Listing_ID',
[property_type_id] as 'Property_Type_ID',
[newspaper_id] as 'Newspaper_ID',
null as 'House_Number',
[House_Street] as 'House_Street',
[Display_Address] as 'Display_Address',
[ZIP] as 'ZIP',
[rental_or_sale] as 'rental_or_Sale',
[price] as 'Price',
[Bedrooms_ID] as 'Bedrooms_ID',
[Comments] as 'description/CDATA',
[Listing_Photo_1] as 'adImages/imges1',
[Listing_Photo_2] as 'adImages/imges2',
[Listing_Photo_3] as 'adImages/imges3',
[Listing_Photo_4] as 'adImages/imges4',
[Listing_Photo_5] as 'adImages/imges5',
[Listing_Photo_6] as 'adImages/imges6',
[Listing_Photo_7] as 'adImages/imges7'
from dbo.vView for xml path('ad'), type, elements
FOR XML path('export')