Solved

XML output as variable

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

Simplifying Server Workload Migrations

This use case outlines the migration challenges that organizations face and how the Acronis AnyData Engine supports physical-to-physical (P2P), physical-to-virtual (P2V), virtual to physical (V2P), and cross-virtual (V2V) migration scenarios to address these challenges.

Question has a verified solution.

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

Suggested Solutions

by Mark Wills Attending one of Rob Farley's seminars the other day, I heard the phrase "The Accidental DBA" and fell in love with it. It got me thinking about the plight of the newcomer to SQL Server...  So if you are the accidental DBA, or, simp…
In SQL Server, when rows are selected from a table, does it retrieve data in the order in which it is inserted?  Many believe this is the case. Let us try to examine for ourselves with an example. To get started, use the following script, wh…
I've attached the XLSM Excel spreadsheet I used in the video and also text files containing the macros used below. https://filedb.experts-exchange.com/incoming/2017/03_w12/1151775/Permutations.txt https://filedb.experts-exchange.com/incoming/201…
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…

756 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