Solved

XML Out

Posted on 2008-10-13
3
182 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

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

So every once in a while at work I am asked to export data from one table and insert it into another on a different server.  I hate doing this.  There's so many different tables and data types.  Some column data needs quoted and some doesn't.  What …
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…
This tutorial demonstrates a quick way of adding group price to multiple Magento products.
Delivering innovative fully-managed cloud services for mission-critical applications requires expertise in multiple areas plus vision and commitment. Meet a few of the people behind the quality services of Concerto.

947 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