Link to home
Start Free TrialLog in
Avatar of cheryl9063
cheryl9063Flag for United States of America

asked on

SQL XML For XML

I need help creating a for xml path that looks like below...I have an outer node and then an inner node...See below for my desired output and regular SQL statement..<promotionInformation>
 is the outermost node and then <expandedPromotionsInformation> is the inner node..

<promotionInformation>
	  <expandedPromotionsInformation>		<promotionType>UserViewed_Value</promotionType>	<listingAddress>AddressLine1</listingAddress>		<promotionStartDate>DateStartPromotion</promotionStartDate>		<promotionEndDate>DateEndPromotion</promotionEndDate>
	</expandedPromotionsInformation>
 <summaryPromotionsInformation>
		<promotionType> UserViewed_Value </promotionType>
		<promotionEndDate> DateEndPromotion </promotionEndDate>
 </summaryPromotionsInformation>
</promotionInformation>



Select UserViewed_Value, AddressLine1 DateStartPromotion, DateEndPromotion
from Promotion

Open in new window

Avatar of cyberkiwi
cyberkiwi
Flag of New Zealand image

Where does "summaryPromotionsInformation" fit?
ASKER CERTIFIED SOLUTION
Avatar of cyberkiwi
cyberkiwi
Flag of New Zealand image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
Avatar of cheryl9063

ASKER

I attached the real code below..

The result looks like this
<promotionInformation>
<expandedPromotionsInformation>
<UserViewed_Value>Open House</UserViewed_Value>
<promotionType>OpenHouse</promotionType>
<listingAddress>425 KENMORE Avenue</listingAddress>
</expandedPromotionsInformation>
</promotionInformation>

But I want this

<promotionInformation>
  <expandedPromotionsInformation>
    <promotionType>OpenHouse</promotionType>
    <listingAddress>425 KENMORE Avenue</listingAddress>
    <PromotionStartDate>null</PromotionStartDate>
    <PromotionEndDate>null</PromotionEndDate>
  </expandedPromotionsInformation>
 <SummaryPromotionsInformation>
      <promotionType>OpenHouse</promotionType>
       <PromotionEndDate>null</PromotionEndDate>
 </SummaryPromotionsInformation>
 </promotionInformation>       
Select pt.UserViewed_Value'expandedPromotionsInformation/UserViewed_Value',
pt.PromotionType'expandedPromotionsInformation/promotionType',
l.ListingAddressLine1'expandedPromotionsInformation/listingAddress',
DATENAME(w,lp.DateStartPromotion)+','+ DATENAME(month,lp.DateStartPromotion)+ ' ' + convert(varchar(25),DATEPART(MONTH,lp.DateStartPromotion))'expandedPromotionsInformation/promotionStartDate',
DATENAME(w,lp.DateEndPromotion)+','+ DATENAME(month,lp.DateEndPromotion)+ ' ' + convert(varchar(25),DATEPART(MONTH,lp.DateEndPromotion))'expandedPromotionsInformation/promotionEndDate'
from Listing l
join promotiontype pt on l.ListingID = pt.PromotionTypeID
join ListingPromotion lp on l.ListingID = lp.ListingPromotionID
 where l.listingID = 100000
for xml path(''), root ('promotionInformation')

Open in new window

First of all, in XML

    <PromotionStartDate>null</PromotionStartDate>
    <PromotionEndDate>null</PromotionEndDate>

That is not valid.  Those would be interpreted downstream as the STRING value NULL.  The database value null is equivalent to the XML empty node, which FOR XML strips out.

Secondly, it would save us both some time to post the original query in the first place.

Lastly, the only trick to making UserViewedValue in two different places is to use it twice in the SELECT clause, with a different path for each , as I have shown in my example.
Thanks!!