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

  • 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?

  • 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

  • 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
  • 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