July 22, 2011 at 11:08 am
Are you looking for something along those lines?
SELECT
CustomerName AS '@name',
(
SELECT DiscountType AS 'discount/@name',
DiscountRate AS 'discount'
FROM @Customer c2 WHERE c1.CustomerName=c2.CustomerName
FOR XML PATH(''), type
)
FROM @Customer c1
GROUP BY CustomerName
FOR XML PATH('customer')
July 22, 2011 at 11:52 am
YES!!! Thank you so much, I've tried several variations but this is the combo that is working - the empty element name with GROUP BY on the outer query seems to be the trick I missed.
July 22, 2011 at 12:24 pm
Do you have a table in your actual database that just lists customers? As per usual normalization practices? That would make this even simpler, and probably faster as well.
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
July 22, 2011 at 1:56 pm
The example I posted was not the actual code, the actual code doesn't deal with customers at all. In any event, the actual data comes from a join between two denormalized datamart tables and I have absolutely no control over the table design - we have a data mgmt. team who owns the physical data model 😀
Lutz's reply got me exactly what I needed
July 25, 2011 at 6:40 am
Cool biz. Sometimes you have to go with "just get it working". That's for sure.
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
Viewing 5 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply