July 22, 2011 at 10:54 am
I'm trying to get the nesting right on a FOR XML PATH query, and have learned that nesting is accomplished via inner/outer queries. I'm very close but just can't get the nesting quite right.
Here's the XML I want:
<customer name="Acme Company">
<discount name="New Order">10.00</discount>
<discount name="ReOrder">20.00</discount>
<discount name="Special">25.00</discount>
</customer>
Here's the data I'm working with:
DECLARE @Customer TABLE
( CustomerName varchar(25)
, DiscountType varchar(25)
, DiscountRate decimal(3,2))
INSERT INTO @Customer(CustomerName, DiscountType, DiscountRate)
VALUES ('Acme Company', 'New Order', .1)
INSERT INTO @Customer(CustomerName, DiscountType, DiscountRate)
VALUES ('Acme Company', 'ReOrder', .2)
INSERT INTO @Customer(CustomerName, DiscountType, DiscountRate)
VALUES ('Acme Company', 'Special', .25)
And here's the query I have so far:
SELECT COuter.CustomerName as [@name]
, (SELECTCInner.DiscountType as [@name]
,COALESCE(CAST(CAST(Cinner.DiscountRate * 100 as decimal(4,2)) AS varchar(20)),'N/A')
FROM@Customer CInner
WHERECOuter.CustomerName = CInner.CustomerName
--AND COuter.DiscountType = CInner.DiscountType
FOR XML PATH('discount'),TYPE
)
FROM
@Customer COuter
FOR XML PATH('customer'), TYPE
This results in the nesting I want but, because this joins my inner and outer queries only on CustomerName, I get a repeating Customer element. The correct JOIN between the inner and outer queries is the commented out line:
AND COuter.DiscountType = CInner.DiscountType
but as soon as I add that I get completely different nesting. What am I doing wrong?
Thanks!
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 6 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply