I have the following tables with the following contents:

create table Employees (EmployeeID int primary key, EmployeeName varchar(10));

insert into Employees values (1, 'Employee1');

insert into Employees values (2, 'Employee2');

insert into Employees values (3, 'Employee3');


create table Orders (EmployeeID int, OrderName varchar(10) primary key);

insert into Orders values (1, 'Order1');

insert into Orders values (1, 'Order2');

insert into Orders values (1, 'Order3');

insert into Orders values (2, 'Order4');

And I want to retrieve the following query in XML

select EmployeeName, OrderName from Orders o, Employees e where o.EmployeeID = e.EmployeeID

The question, how can I save me the repeated EmployeeNames in XML? So that to have an output like:

    <orders>

    <employeename>Employee1

    <ordername>Order1</ordername>

    <ordername>Order2</ordername>

    <ordername>Order3</ordername>

    </employeename>

    <employeename>Employee2

    <ordername>Order4</ordername>

    </employeename>

    </orders>