Generate html from sql table

Question

Is it possible from this example table:

CREATE TABLE #Actions(EmployeeId INT,EmployeeName VARCHAR(100),ActionStart TIME,ActionEnd TIME,Type VARCHAR(10));
INSERT INTO #Actions(EmployeeId,EmployeeName,ActionStart,ActionEnd, Type)
VALUES (1,'Bob','09:00','12:00', 'action'),(1,'Bob','14:30','16:00', 'action'),(1,'Bob','18:00','20:00', 'event'),(2,'Susan','10:00','12:00', 'action');

to have this output in XML?

<div class="employee" employeeid="1" employeename="Bob">
    <div class="action" start="09:00" end="12:00" type="action"></div>
    <div class="action" start="14:30" end="16:00" type="action"></div>
    <div class="action" start="18:00" end="20:00" type="event"></div>
</div>
<div class="employee" employeeid="2" employeename="Susan">
    <div class="action" start="10:00" end="12:00" type="action"></div>
</div>

Mikael Eriksson · Accepted Answer · 2011-11-16 14:11:57Z

2

select 'employee' as '@class',
       a1.EmployeeId as '@employeeid',
       max(a1.EmployeeName) as '@employeename',
       (select 'action' as '@class',
               left(a2.ActionStart, 5) as '@start',
               left(a2.ActionEnd, 5) as '@end',
               a2.Type as '@type'
        from #Actions as a2
        where a1.EmployeeId = a2.EmployeeId
        for xml path('div'), type)
from #Actions as a1
group by a1.EmployeeId
for xml path('div')

edited Nov 16, 2011 at 14:11

answered Nov 16, 2011 at 13:56

Mikael Eriksson

139k22 gold badges223 silver badges293 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

John Over a year ago

Piece of cake. I have never worked with XML in SQL and didn't see that a simple subquery would solve the problem. Thanks.

Collectives™ on Stack Overflow

Generate html from sql table

1 Answer 1

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related