Cascade Grouping sql rows into Xml nodes

I have the following rows:

ID|Customer | Part Number | Part Number Price | Hardware ID | Hardware Value
------------------------------------------------------------------------------
1 | John    |     15      |       10          |      1      | 1000
2 | John    |     16      |       15          |      2      | 500

The output i'm trying to get in SQL Server is the following:

<Order>
 <Cu开发者_运维知识库stomer>John</Customer>
 <PartNumbers>
  <PartNumber>15</PartNumber><PartNumberPrice>10</PartNumberPrice>  
  <PartNumber>16</PartNumber><PartNumberPrice>15</PartNumberPrice>  
 </PartNumbers>
 <Hardware>
  <HardwareId>1</HardwareId><HardwareValue>1000</HardwareValue>
  <HardwareId>1</HardwareId><HardwareValue>500</HardwareValue>
 </Hardware>
</Orders>

Any idea how to solve this one?

Thanks!

declare @T table
(
  ID int,
  Customer varchar(10),
  [Part Number] int,
  [Part Number Price] int,
  [Hardware ID] int,
  [Hardware Value] int
)

insert into @T values
(1, 'John', 15, 10, 1, 1000),
(2, 'John', 16, 15, 2, 500)

select T1.Customer as Customer,
       (select T2.[Part Number] as PartNumber,
               T2.[Part Number Price] as PartNumberPrice
        from @T as T2
        where T1.Customer = T2.Customer       
        for xml path(''), root('PartNumbers'), type),
       (select T2.[Hardware ID] as HardwareId,
               T2.[Hardware Value] as HardwareValue
        from @T as T2
        where T1.Customer = T2.Customer       
        for xml path(''), root('Hardware'), type)
from @T as T1
group by T1.Customer
for xml path(''), root('Order')

This may look a little funny with the self joins but that is only because your tables are not properly normalized. You might want to consider column names without spaces as well.

SELECT Customer as Customer,
      (SELECT DISTINCT o.[Part Number] partNumber,o.[Part Number Price] PartNumberPrice
       FROM yTable o
       where t.id = o.id
       FOR XML AUTO, TYPE),
      (SELECT DISTINCT x.[Hardware ID] hardwareid,x.[Hardware Value] hardwarevalue
       FROM yTable x
       where t.id = x.id
       FOR XML AUTO, TYPE)
FROM yTable t
FOR XML AUTO, TYPE