Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 409
  • Last Modified:

Select for XML Output

I have a select statement attached that produces this data.
<export>
  <ad>
    <feedPublishDate>11/12/2008</feedPublishDate>
    <title><![CDATA[Coral Ridge]]></title>
    <description><![CDATA[My Data]]></description>
    <adimages><![CDATA[]]></adimages>
    <images><![CDATA[image1.jpg]]></images>
    <images><![CDATA[image1.jpg]]></images>
    <images><![CDATA[image1.jpg]]></images>
    <images><![CDATA[image1.jpg]]></images>
    <images><![CDATA[image1.jpg]]></images>
    <images><![CDATA[image1.jpg]]></images>
    <images><![CDATA[image1.jpg]]></images>
    <images><![CDATA[image1.jpg]]></images>
  </ad>
</export>

I NEED it to look like this

<export>
  <feedPublishDate>11/12/2008</feedPublishDate>
  <ad version ="1" catalog="23">
    <title><![CDATA[Coral Ridge]]></title>
    <description><![CDATA[My Data]]></description>
    <adimages><![CDATA[]]></adimages>
    <adImages>
      <images>
        <image><![CDATA[image1.jpg]]></image>
        <image><![CDATA[image1.jpg]]></image>
        <image><![CDATA[image1.jpg]]></image>
        <image><![CDATA[image1.jpg]]></image>
        <image><![CDATA[image1.jpg]]></image>
        <image><![CDATA[image1.jpg]]></image>
        <image><![CDATA[image1.jpg]]></image>
        <image><![CDATA[image1.jpg]]></image>
      </images>
    </adImages>    
  </ad>
</export>
select
 1 as Tag, null as Parent, 
CONVERT (Varchar(10), getdate(), 101) as [ad!1!feedPublishDate!element],
	title as [ad!1!title!cdata],
	'My Data' as [ad!1!description!cdata],
	'' as [ad!1!adimages!cdata],
			'image1.jpg' as [ad!1!images!cdata],
            'image1.jpg' as [ad!1!images!cdata],
            'image1.jpg' as [ad!1!images!cdata],
            'image1.jpg' as [ad!1!images!cdata],
            'image1.jpg' as [ad!1!images!cdata],
            'image1.jpg' as [ad!1!images!cdata],
            'image1.jpg' as [ad!1!images!cdata],
            'image1.jpg' as [ad!1!images!cdata]
from dbo.vSecondSpace_baseData where mlsid = 'F868140'
for xml explicit, root('export')

Open in new window

0
lrbrister
Asked:
lrbrister
  • 2
1 Solution
 
eszaqCommented:
Try starting your code with:

select
 1 as Tag, null as Parent,
CONVERT (Varchar(10), getdate(), 101) as [feedPublishDate!element],
      1 as [ad!1!version],
      23 as [ad!1!catalog],
      title as [ad!1!title!cdata],

... and so on as in your snippet
0
 
lrbristerAuthor Commented:
This put me on the right track
0
 
eszaqCommented:
I was hoping it would. Glad it helped.
0

Featured Post

Efficient way to get backups off site to Azure

This user guide provides instructions on how to deploy and configure both a StoneFly Scale Out NAS Enterprise Cloud Drive virtual machine and Veeam Cloud Connect in the Microsoft Azure Cloud.

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