Go Premium for a chance to win a PS4. Enter to Win

x
?
Solved

XML output as variable

Posted on 2008-10-16
3
Medium Priority
?
901 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
  • 2
3 Comments
 
LVL 37

Accepted Solution

by:
momi_sabag earned 2000 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

Concerto's Cloud Advisory Services

Want to avoid the missteps to gaining all the benefits of the cloud? Learn more about the different assessment options from our Cloud Advisory team.

Question has a verified solution.

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

Introduction: When running hybrid database environments, you often need to query some data from a remote db of any type, while being connected to your MS SQL Server database. Problems start when you try to combine that with some "user input" pass…
Use this article to create a batch file to backup a Microsoft SQL Server database to a Windows folder.  The folder can be on the local hard drive or on a network share.  This batch file will query the SQL server to get the current date & time and wi…
We’ve all felt that sense of false security before—locking down external access to a database or component and feeling like we’ve done all we need to do to secure company data. But that feeling is fleeting. Attacks these days can happen in many w…
Screencast - Getting to Know the Pipeline

916 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