Problem creating XML with FOR XML PATH

Using Northwind as an example this is the XML I would like to create:

<CustomerOrders>
<Customer CustomerID="ALFKI" CompanyName="Alfreds Futterkiste">
<Orders>
<Order orderID="10643" orderdate="08/25/1997" />
<Order orderID="10692" orderdate="10/03/1997" />
<Order orderID="10702" orderdate="10/13/1997" />
<Order orderID="10835" orderdate="01/15/1998" />
<Order orderID="10952" orderdate="03/16/1998" />
<Order orderID="11011" orderdate="04/09/1998" />
</Orders>
</Customer>
</CustomerOrders>

However when I run the following SQL, Below is what I create.
Could some one show me how to change my query to get the correct results above?

use northwind

select
Customers.CustomerID 'Customer/@.CustomerID',
CompanyName 'Customer/@.CompanyName',
orderID 'Customer/Orders/Order/@.orderID',
convert(nvarchar(10),OrderDate,101) 'Customer/Orders/Order/@.orderdate'
from Customers
join Orders on Customers.CustomerID =Orders.CustomerID
where Customers.CustomerID='ALFKI'
FOR XML PATH ('CustomerOrders')

Partial Result of query:

<CustomerOrders>
<Customer CustomerID="ALFKI" CompanyName="Alfreds Futterkiste">
<Orders>
<Order orderID="10643" orderdate="08/25/1997" />
</Orders>
</Customer>
</CustomerOrders>
<CustomerOrders>
<Customer CustomerID="ALFKI" CompanyName="Alfreds Futterkiste">
<Orders>
<Order orderID="10692" orderdate="10/03/1997" />
</Orders>
</Customer>
</CustomerOrders>...

Mickey:

My first pass at the problem looks like this:

Code Snippet

declare @.customers table
( CustomerID varchar(12),
CompanyName varchar(25)
)
insert into @.customers
select 'ALFKI', 'Alfreds Futterkiste'

declare @.orders table
( customerId varchar(12),
orderId integer,
orderDate datetime
)
insert into @.orders
select 'ALFKI', 10643, '8/25/1997' union all
select 'ALFKI', 10692, '10/3/1997' union all
select 'ALFKI', 10702, '10/13/1997' union all
select 'ALFKI', 10835, '1/15/98' union all
select 'ALFKI', 10952, '3/17/98' union all
select 'ALFKI', 11011, '4/9/98'
--select * from @.orders

select replace(replace(
(
select customerId 'Customer/@.CustomerID',
companyName 'Customer/@.CompanyName',
( select orderId 'Order/@.OrderID',
convert(varchar, orderDate, 101) 'Order/@.orderdate'
from @.orders b
for xml path('')
) 'Customer/Orders'
from @.customers
for xml path(''), root('CustomerOrders')
), '&lt;', '<'), '&gt;', '>')
as theXml

/*
theXml

<CustomerOrders><Customer CustomerID="ALFKI" CompanyName="Alfreds Futterkiste"><Orders><Order OrderID="10643" orderdate="08/25/1997"/><Order OrderID="10692" orderdate="10/03/1997"/><Order OrderID="10702" orderdate="10/13/1997"/><Order OrderID="10835" orderdate="01/15/1998"/><Order OrderID="10952" orderdate="03/17/1998"/><Order OrderID="11011" orderdate="04/09/1998"/></Orders></Customer></CustomerOrders>
*/

/* Manually Reformatted:
<CustomerOrders>
<Customer CustomerID="ALFKI" CompanyName="Alfreds Futterkiste">
<Orders>
<Order OrderID="10643" orderdate="08/25/1997"/>
<Order OrderID="10692" orderdate="10/03/1997"/>
<Order OrderID="10702" orderdate="10/13/1997"/>
<Order OrderID="10835" orderdate="01/15/1998"/>
<Order OrderID="10952" orderdate="03/17/1998"/>
<Order OrderID="11011" orderdate="04/09/1998"/>
</Orders>
</Customer>
</CustomerOrders>
*/

|||

After some research I was able to find a solution using a subselect.

Here is the code. Thanks to anyone who spent any time on this.

use northwind

select

Customers.CustomerID 'Customer/@.CustomerID',

CompanyName 'Customer/@.CompanyName',

(select

orderID '@.orderID',

convert(nvarchar(10),OrderDate,101) '@.orderdate'

from Orders

where Customers.CustomerID =Orders.CustomerID

FOR XML PATH ('Order'), Type ) 'Customer/Orders'

from Customers

where Customers.CustomerID='ALFKI'

FOR XML PATH ('CustomerOrders')