Solved

XML output as variable

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

How your wiki can always stay up-to-date

Quip doubles as a “living” wiki and a project management tool that evolves with your organization. As you finish projects in Quip, the work remains, easily accessible to all team members, new and old.
- Increase transparency
- Onboard new hires faster
- Access from mobile/offline

Join & Write a Comment

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…
Here's a very brief overview of the methods PRTG Network Monitor (https://www.paessler.com/prtg) offers for monitoring bandwidth, to help you decide which methods you´d like to investigate in more detail.  The methods are covered in more detail in o…
In this tutorial you'll learn about bandwidth monitoring with flows and packet sniffing with our network monitoring solution PRTG Network Monitor (https://www.paessler.com/prtg). If you're interested in additional methods for monitoring bandwidt…

760 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

24 Experts available now in Live!

Get 1:1 Help Now