Solved

XML output as variable

Posted on 2008-10-16
3
896 Views
Last Modified: 2012-05-05
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>'
 
 
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] as 'description/CDATA',
			[Full_baths],
			[Acres],
			[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')

Open in new window

0
Comment
Question by:lrbrister
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 2
3 Comments
 
LVL 37

Accepted Solution

by:
momi_sabag earned 500 total points
ID: 22729896
declare @output xml

set @output = (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] as 'description/CDATA',
                  [Full_baths],
                  [Acres],
                  [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')
)
0
 

Author Comment

by:lrbrister
ID: 22730014
momi_sabag:
When I insert your code into my SP I get a "successful" message.

When I add
select @output afterwards I'm getting a nocolumn name and blank field

When I add return @output I get
Operand type clash: xml is incompatible with int

Do I need to cast or convert my integer data fields that are getting returned?
0
 

Author Comment

by:lrbrister
ID: 22730101
Vever mind...added cast to select
0

Featured Post

Migrating Your Company's PCs

To keep pace with competitors, businesses must keep employees productive, and that means providing them with the latest technology. This document provides the tips and tricks you need to help you migrate an outdated PC fleet to new desktops, laptops, and tablets.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

If you having speed problem in loading SQL Server Management Studio, try to uncheck these options in your internet browser (IE -> Internet Options / Advanced / Security):    . Check for publisher's certificate revocation    . Check for server ce…
There are some very powerful Data Management Views (DMV's) introduced with SQL 2005. The two in particular that we are going to discuss are sys.dm_db_index_usage_stats and sys.dm_db_index_operational_stats.   Recently, I was involved in a discu…
Exchange organizations may use the Journaling Agent of the Transport Service to archive messages going through Exchange. However, if the Transport Service is integrated with some email content management application (such as an antispam), the admini…
This video shows how to use Hyena, from SystemTools Software, to update 100 user accounts from an external text file. View in 1080p for best video quality.

752 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question