select for xml, nest on order by fields

SELECT 
      [Represantative] 
      ,[log_date] 
      ,[customer] 
      ,[type] 
      ,[log_type] 
      ,[zone_code] 
  FROM [dbo].[ilc_ziyaret_plani_gun_degisiklikleri_v] gun_logu
order by Represantative, log_date, customer
for xml path ,  root('log_details')

I need the xml nested as in the order by clause: I tried using for xml 开发者_Python百科path clause, but it did not do the nesting, it just shaped the xml doc. Represantative, log_date, customer

<log_details>
 <represantative name="john doe">
  <log_date date="2010-02-10">
   <customer c="xyz">
    <op>
      <log_type="add"/>
      <zone_code="a1"/>
    </op>
    <op>
      <log_type="add"/>
      <zone_code="b1"/>
    </op>
   <customer/>
   <customer> same as above </customer>
  <log_date>same for next date</log_date>

any idea how to do this

Formatting xml in TSQL takes some trial and error

SELECT  
      [Represantative] 
      ,[log_date]  
      ,[customer]  "customer/@name"
      ,[type]  "customer/@type"
      ,[log_type] "customer/op/log_type" 
      ,[zone_code]  "customer/op/zone_code"
  from t1
order by Represantative, log_date, customer 
for xml path('log_detail') ,  root('log_details') 

returns this xml output

<log_details>
  <log_detail>
    <Represantative>john doe</Represantative>
    <log_date>2010-02-10</log_date>
    <customer name="xyz" type="add">
      <op>
        <log_type>a1</log_type>
        <zone_code>b1</zone_code>
      </op>
    </customer>
  </log_detail>
</log_details>