SQL Server XML Output

ALTER PROCEDURE [dbo].[XML_Reports]
 
AS
BEGIN
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>'
 
select
--Non-Recurring header Section
(Select @title),
(Select @link),
(Select @description),
(Select @lastBuildDate),
(Select @language),
--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',
[Agent_ID],
[Agent_Contact_1],
[Agent_Phone_1],
[Agent_Email_1],
[Agent_Picture_1],
[Comments],
[Full_baths],
[Acres],
[Listing_Photo_1],
[Listing_Photo_2],
[Listing_Photo_3],
[Listing_Photo_4],
[Listing_Photo_5],
[Listing_Photo_6],
[Listing_Photo_7]
from dbo.myView for xml path('ad'), type, elements
) 
FOR XML path('export')
END

Select allOpen in new window