Is it possible to transform the data to xml file with specified format

• kpao

  SSC-Addicted

  Points: 406

  More actions

  April 13, 2011 at 2:01 am

  #235648

  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?

• Hunterwood

  SSCrazy

  Points: 2074

  More actions

  April 13, 2011 at 3:59 am

  #1310896

  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

• Mark Cowne

  One Orange Chip

  Points: 26951

  More actions

  April 13, 2011 at 4:17 am

  #1310905

  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/61537

• kpao

  SSC-Addicted

  Points: 406

  More actions

  April 13, 2011 at 9:36 pm

  #1311408

  Thanks for the answer.