April 13, 2011 at 2:01 am
Suppose I have a table A:
ID Bid BranchName WeekRange OfficeHours
1B1Branch 1Monday to Thursday09:00 - 13:00
2B1Branch 1Friday 14:00 - 18:00
3B2Branch 2Monday to Friday 09:00 - 12:00
4B3Branch 3Monday to Friday 09:00 - 13:00
5B4Branch 4Monday to Sunday09:00 - 13:00
I would like to transform this data into xml such like the below result:
<Offices>
<Office>
<Bid>B1</Bid>
<BranchName>Branch 1</BranchName>
<WorkingDayRange>
<Range1>
Monday to Thursday 09:00 - 13:00
</Range1>
<Range2>
Friday 14:00 - 18:00
</Range2>
</WorkingDayRange>
</Office>
<Office>
<Bid>B2</Bid>
<BranchName>Branch 2</BranchName>
<WorkingDayRange>
<Range1>
Monday to Friday 09:00 - 12:00
</Range1>
</WorkingDayRange>
</Office>
<Office>
<Bid>B3</Bid>
<BranchName>Branch 3</BranchName>
<WorkingDayRange>
<Range1>
Monday to Friday 09:00 - 13:00
</Range1>
</WorkingDayRange>
</Office>
<Office>
<Bid>B5</Bid>
<BranchName>Branch 5</BranchName>
<WorkingDayRange>
<Range1>
Monday to Sunday 09:00 - 13:00
</Range1>
</WorkingDayRange>
</Office>
</Offices>
I have searched about xml in SQL Server 2005. The data can be transformed into xml. But the structure is simple. Is there any way to achieve that?
April 13, 2011 at 3:59 am
Hi kpao,
I'm not sure how to get (if it is possible at all) numbered tag names (<Range1>, <Range2>, etc). If you are satisfied by getting the numbers as attributes instead (<Range x="1">, <Range x="2">, etc), you could use this code:
declare @test-2 as table
(
IDint,
Bidchar(2),
Bid_idint,
BranchNamevarchar(10),
WeekRangevarchar(50),
OfficeHoursvarchar(50)
)
insert into @test-2
select1, 'B1', 1, 'Branch 1', 'Monday to Thursday', '09:00 - 13:00'
union all
select2, 'B1', 1, 'Branch 1', 'Friday', '14:00 - 18:00'
union all
select3, 'B2', 2, 'Branch 2', 'Monday to Friday', '09:00 - 12:00'
union all
select4, 'B3', 3, 'Branch 3', 'Monday to Friday', '09:00 - 13:00'
union all
select5, 'B4', 4, 'Branch 4', 'Monday to Sunday', '09:00 - 13:00'
select Bid, BranchName,
(select row_number() over (ORDER BY ID) as '@x', WeekRange + ' ' + OfficeHours
from @test-2 R
where R.Bid = Main.Bid
for xml path('Range'), root ('WorkingDayRange'), type)
from @test-2 Main
group by Bid, BranchName
for xml path ('Office'), root ('Offices'), type
This would yield the result:
<Offices>
<Office>
<Bid>B1</Bid>
<BranchName>Branch 1</BranchName>
<WorkingDayRange>
<Range x="1">Monday to Thursday 09:00 - 13:00</Range>
<Range x="2">Friday 14:00 - 18:00</Range>
</WorkingDayRange>
</Office>
<Office>
<Bid>B2</Bid>
<BranchName>Branch 2</BranchName>
<WorkingDayRange>
<Range x="1">Monday to Friday 09:00 - 12:00</Range>
</WorkingDayRange>
</Office>
<Office>
<Bid>B3</Bid>
<BranchName>Branch 3</BranchName>
<WorkingDayRange>
<Range x="1">Monday to Friday 09:00 - 13:00</Range>
</WorkingDayRange>
</Office>
<Office>
<Bid>B4</Bid>
<BranchName>Branch 4</BranchName>
<WorkingDayRange>
<Range x="1">Monday to Sunday 09:00 - 13:00</Range>
</WorkingDayRange>
</Office>
</Offices>
Hope this helps!
Regards,
Markus
April 13, 2011 at 4:17 am
WITH Pivotted AS (
SELECT Bid,
BranchName,
WeekRange,
OfficeHours,
ROW_NUMBER() OVER(PARTITION BY Bid,BranchName ORDER BY BranchName) As rn
FROM TableA)
SELECT Bid,
BranchName,
MAX(CASE WHEN rn=1 THEN WeekRange + ' ' + OfficeHours END) AS "WorkingDayRange/Range1",
MAX(CASE WHEN rn=2 THEN WeekRange + ' ' + OfficeHours END) AS "WorkingDayRange/Range2",
MAX(CASE WHEN rn=3 THEN WeekRange + ' ' + OfficeHours END) AS "WorkingDayRange/Range3",
MAX(CASE WHEN rn=4 THEN WeekRange + ' ' + OfficeHours END) AS "WorkingDayRange/Range4"
FROM Pivotted
GROUP BY Bid,BranchName
FOR XML PATH('Office'),ROOT('Offices'),TYPE;
____________________________________________________
Deja View - The strange feeling that somewhere, sometime you've optimised this query before
How to get the best help on a forum
http://www.sqlservercentral.com/articles/Best+Practices/61537April 13, 2011 at 9:36 pm
Thanks for the answer.
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply