• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 193
  • Last Modified:

XML Out

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
lrbrister
Asked:
lrbrister
  • 2
1 Solution
 
momi_sabagCommented:
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
 
lrbristerAuthor Commented:
momi_sabag:
You have an example?
0
 
lrbristerAuthor Commented:
And I still need the "CDATA" part of my question
0

Featured Post

2018 Annual Membership Survey

Here at Experts Exchange, we strive to give members the best experience. Help us improve the site by taking this survey today! (Bonus: Be entered to win a great tech prize for participating!)

  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now