August 12, 2015 at 5:28 am
Hi all, i am hoping someone can point me in the right direction.
Simply put; I have a de-normalised table that I need to export to XML using For XML, but put all of the related rows under the same node.
The table is alot more complicated than the example below, but for proof of concept purposes, i'll keep it really simple:
Campaign, Price
C1, 4.00
C1, 6.00
C1, 10.00
C2, 1.00
C2, 13.00
C3, 20.00
If I have a table of campaigns and prices, I would like to output it as XML like the following:
<Campaign name="C1">
<Price value="4.00"></Price>
<Price value="6.00"></Price>
<Price value="10.00"></Price>
</Campaign>
<Campaign name="C2">
<Price value="1.00"></Price>
etc
The default behaviour gives me this:
<Campaign name="C1" price="4.00"/>
<Campaign name="C1" price="6.00"/>
<Campaign name="C1" price="10.00"/>
Is there an existing option that deals with this automatically, or do I essentially need to do a group by to output the campaign element, and then union an ungrouped select to output the price element?
Kind regards,
Phil
August 12, 2015 at 6:13 am
Thats the closest i could get
SELECT Campaign [@name],
(
SELECT Price [@value]
FROM TableName P
WHERE P.Campaign = C.Campaign
FOR XML PATH('Price'), TYPE)
FROM (
SELECT DISTINCT
Campaign
FROM TableName) C
FOR XML PATH ('Campaign')
<Campaign name="C1">
<Price value="4.00" />
<Price value="6.00" />
<Price value="10.00" />
</Campaign>
<Campaign name="C2">
<Price value="1.00" />
<Price value="13.00" />
</Campaign>
<Campaign name="C3">
<Price value="20.00" />
</Campaign>
EDIT: I hate parsing XMLs in T-SQL
August 12, 2015 at 6:25 am
Quick solution, similar to Kutang Pan's but slightly more flexible
😎
USE tempdb;
GO
SET NOCOUNT ON;
DECLARE @SAMPLE_DATA TABLE
(
Campaign CHAR(2) NOT NULL
,Price DECIMAL(16,2) NOT NULL
);
INSERT INTO @SAMPLE_DATA(Campaign, Price)
VALUES
('C1',4.00 )
,('C1',6.00 )
,('C1',10.00)
,('C2',1.00 )
,('C2',13.00)
,('C3',20.00);
;WITH CAMPAIGN AS
(
SELECT
DISTINCT S.Campaign
FROM @SAMPLE_DATA S
)
SELECT
C.Campaign AS '@name'
,(
SELECT
SD.Price AS 'Price/@value'
FROM @SAMPLE_DATA SD
WHERE SD.Campaign = C.Campaign
FOR XML PATH(''),TYPE
)
FROM CAMPAIGN C
FOR XML PATH('Campaign')
Results
<Campaign name="C1">
<Price value="4.00" />
<Price value="6.00" />
<Price value="10.00" />
</Campaign>
<Campaign name="C2">
<Price value="1.00" />
<Price value="13.00" />
</Campaign>
<Campaign name="C3">
<Price value="20.00" />
</Campaign>
August 12, 2015 at 6:39 am
Also for the xml to have the correct format it can't have multimple root level elements as it has now.
August 12, 2015 at 6:57 am
Excellent, thanks both for your replies. I'll give them a try shortly, but it looks like they'll work perfectly.
Also, apologies, I forgot to paste the root node into the example!
Kind regards,
Phil
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply