Want to win a PS4? Go Premium and enter to win our High-Tech Treats giveaway. Enter to Win

x
?
Solved

XML Out

Posted on 2008-10-13
3
Medium Priority
?
192 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 2000 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

Get your Disaster Recovery as a Service basics

Disaster Recovery as a Service is one go-to solution that revolutionizes DR planning. Implementing DRaaS could be an efficient process, easily accessible to non-DR experts. Learn about monitoring, testing, executing failovers and failbacks to ensure a "healthy" DR environment.

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…
I've encountered valid database schemas that do not have a primary key.  For example, I use LogParser from Microsoft to push IIS logs into a SQL database table for processing and analysis.  However, occasionally due to user error or a scheduled task…
This tutorial will teach you the special effect of super speed similar to the fictional character Wally West aka "The Flash" After Shake : http://www.videocopilot.net/presets/after_shake/ All lightning effects with instructions : http://www.mediaf…
Is your data getting by on basic protection measures? In today’s climate of debilitating malware and ransomware—like WannaCry—that may not be enough. You need to establish more than basics, like a recovery plan that protects both data and endpoints.…

636 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