Solved

XML Out

Posted on 2008-10-13
3
180 Views
Last Modified: 2010-03-19
I have to take
colImage1, colImage2, colImage3
and output as XML so that I get

<adImages>
      <images>
        <image><![CDATA[http://www.mydomain.com/test1.jpg]]></image>
        <image><![CDATA[http://www.mydomain.com/test2.jpg]]></image>
      </images>
    </adImages>

The attached code gets me

<ad>
    blah...blah...blah...
    <Acres />
    <adImages>
      <imges1>http://www.mysomain.com/images/F868140_01.jpg</imges1>
      <imges2>http://www.mysomain.com/images/F868140_02.jpg</imges2>
      <imges3>http://www.mysomain.com/images/F868140_03.jpg</imges3>
      <imges4>http://www.mysomain.com/images/F868140_04.jpg</imges4>
      <imges5>http://www.mysomain.com/images/F868140_05.jpg</imges5>
      <imges6>http://www.mysomain.com/images/F868140_06.jpg</imges6>
      <imges7>http://www.mysomain.com/images/F868140_07.jpg</imges7>
    </adImages>
</ad>
<ad>

etc...


(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.vView1 for xml path('ad'), type, elements

) 

FOR XML path('export')

END

Open in new window

0
Comment
Question by:lrbrister
  • 2
3 Comments
 
LVL 37

Accepted Solution

by:
momi_sabag earned 500 total points
ID: 22719609
you can't have more than one columned named as the same element,
you will have to use some sort of join for that
0
 

Author Comment

by:lrbrister
ID: 22720517
momi_sabag:
You have an example?
0
 

Author Comment

by:lrbrister
ID: 22729747
And I still need the "CDATA" part of my question
0

Featured Post

Free Gift Card with Acronis Backup Purchase!

Backup any data in any location: local and remote systems, physical and virtual servers, private and public clouds, Macs and PCs, tablets and mobile devices, & more! For limited time only, buy any Acronis backup products and get a FREE Amazon/Best Buy gift card worth up to $200!

Join & Write a Comment

INTRODUCTION: While tying your database objects into builds and your enterprise source control system takes a third-party product (like Visual Studio Database Edition or Red-Gate's SQL Source Control), you can achieve some protection using a sing…
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.
This video discusses moving either the default database or any database to a new volume.
This demo shows you how to set up the containerized NetScaler CPX with NetScaler Management and Analytics System in a non-routable Mesos/Marathon environment for use with Micro-Services applications.

708 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

13 Experts available now in Live!

Get 1:1 Help Now