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

• kpao

  Old Hand

  Points: 356

  More actions

  April 13, 2011 at 2:01 am

  #235648

  Suppose I have a table A:

  ID Bid BranchName WeekRange OfficeHours

  1 B1 Branch 1 Monday to Thursday 09:00 - 13:00

  2 B1 Branch 1 Friday 14:00 - 18:00

  3 B2 Branch 2 Monday to Friday 09:00 - 12:00

  4 B3 Branch 3 Monday to Friday 09:00 - 13:00

  5 B4 Branch 4 Monday to Sunday 09: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

  (

  ID int,

  Bid char(2),

  Bid_id int,

  BranchName varchar(10),

  WeekRange varchar(50),

  OfficeHours varchar(50)

  )

  insert into @test-2

  select 1, 'B1', 1, 'Branch 1', 'Monday to Thursday', '09:00 - 13:00'

  union all

  select 2, 'B1', 1, 'Branch 1', 'Friday', '14:00 - 18:00'

  union all

  select 3, 'B2', 2, 'Branch 2', 'Monday to Friday', '09:00 - 12:00'

  union all

  select 4, 'B3', 3, 'Branch 3', 'Monday to Friday', '09:00 - 13:00'

  union all

  select 5, '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: 26865

  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

  Old Hand

  Points: 356

  More actions

  April 13, 2011 at 9:36 pm

  #1311408

  Thanks for the answer.