SQL Server : nesting elements with FOR XML PATH -

i want nest each of xml elements.

take following example:

declare @temptable table (     [column1] char(10),     [column2] char(10) );  insert @temptable([column1], [column2]) values ('some value', 'some value'), ('some value', 'some value'), ('some value', 'some value'), ('some value', 'some value')  select (     select * @temptable xml path('row'), type) xml path('parentrow'), root('root') 

which return following xml:

  <root>     <parentrow>       <row>         <column1>some value</column1>         <column2>some value</column2>       </row>       <row>         <column1>some value</column1>         <column2>some value</column2>       </row>       <row>         <column1>some value</column1>         <column2>some value</column2>       </row>       <row>         <column1>some value</column1>         <column2>some value</column2>       </row>     </parentrow>   </root> 

which not way want xml formatted. rather want each <row> element wrapped <parentrow> element below:

  <root>     <parentrow>       <row>         <column1>some value</column1>         <column2>some value</column2>       </row>     </parentrow>     <parentrow>     <row>       <column1>some value</column1>       <column2>some value</column2>     </row>     </parentrow>     <parentrow>       <row>         <column1>some value</column1>         <column2>some value</column2>       </row>     </parentrow>     <parentrow>       <row>         <column1>some value</column1>         <column2>some value</column2>       </row>     </parentrow>   </root>     

any guys?

okay here code.

declare @temptable table (     [column1] char(10),     [column2] char(10) );  insert @temptable([column1], [column2]) values ('some value', 'some value'), ('some value', 'some value'), ('some value', 'some value'), ('some value', 'some value')  select (         select (                 select t1.*                 xml path('')                     ,root('row')                     ,type                 )         @temptable t1         xml path('parentrow')             ,type         ) xml path('root') 

and here output.

<root>   <parentrow>     <row>       <column1>some value</column1>       <column2>some value</column2>     </row>   </parentrow>   <parentrow>     <row>       <column1>some value</column1>       <column2>some value</column2>     </row>   </parentrow>   <parentrow>     <row>       <column1>some value</column1>       <column2>some value</column2>     </row>   </parentrow>   <parentrow>     <row>       <column1>some value</column1>       <column2>some value</column2>     </row>   </parentrow> </root> 

good luck...