Solved

XML Out

Posted on 2008-10-13
3
189 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 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

Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

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

I am showing a way to read/import the excel data in table using SQL server 2005... Suppose there is an Excel file "Book1" at location "C:\temp" with column "First Name" and "Last Name". Now to import this Excel data into the table, we will use…
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 …
Michael from AdRem Software explains how to view the most utilized and worst performing nodes in your network, by accessing the Top Charts view in NetCrunch network monitor (https://www.adremsoft.com/). Top Charts is a view in which you can set seve…
This is my first video review of Microsoft Bookings, I will be doing a part two with a bit more information, but wanted to get this out to you folks.

688 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