Solved

XML output as variable

Posted on 2008-10-16
3
887 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 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

What is SQL Server and how does it work?

The purpose of this paper is to provide you background on SQL Server. It’s your self-study guide for learning fundamentals. It includes both the history of SQL and its technical basics. Concepts and definitions will form the solid foundation of your future DBA expertise.

Question has a verified solution.

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

Suggested Solutions

In this article I will describe the Backup & Restore method as one possible migration process and I will add the extra tasks needed for an upgrade when and where is applied so it will cover all.
In this article we will get to know that how can we recover deleted data if it happens accidently. We really can recover deleted rows if we know the time when data is deleted by using the transaction log.
Windows 10 is mostly good. However the one thing that annoys me is how many clicks you have to do to dial a VPN connection. You have to go to settings from the start menu, (2 clicks), Network and Internet (1 click), Click VPN (another click) then fi…
Both in life and business – not all partnerships are created equal. As the demand for cloud services increases, so do the number of self-proclaimed cloud partners. Asking the right questions up front in the partnership, will enable both parties …

863 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

Need Help in Real-Time?

Connect with top rated Experts

20 Experts available now in Live!

Get 1:1 Help Now