SQL Server FOR XML PATH nesting issue

Question

I am trying to convert a query to return as xml in the correct format

The query is simply SELECT * FROM [TABLENAME], and yes it has to be * as this is used for a dynamic utility that needs to work on any table.

Anyhow, I want the resulting records to be nested two levels deep like this

<Root>
   <RecordParent>
      <Record>
          <!--Fields selected with * end up here-->
      </Record>
   </RecordParent>
   <RecordParent>
      <Record>
          <!--Fields selected with * end up here-->
      </Record>
   </RecordParent>
   <RecordParent>
      <Record>
          <!--Fields selected with * end up here-->
      </Record>
   </RecordParent>
</Root>

But I can't seem to get this xml, the best I've done is

SELECT ( 
    SELECT * FROM tableName FOR XML PATH('Record'), TYPE
) FOR XML PATH('RecordParent'), ROOT('Root')

Which ends up like

<Root>
   <RecordParent>
      <Record>
          <!--Fields selected with * end up here-->
      </Record>
      <Record>
          <!--Fields selected with * end up here-->
      </Record>
      <Record>
          <!--Fields selected with * end up here-->
      </Record>
   </RecordParent>
</Root>

Any ideas how I can get the correct result with FOR XML?

EDIT

I can do it in a few lines

DECLARE @xml xml
SET @xml = (SELECT TOP 10 * FROM tableName FOR XML PATH('Record'), ROOT('Records'))
SELECT  r.query('.')FROM @xml.nodes('//Record') R(r) FOR XML PATH('RecordParent'), ROOT('Root'), TYPE

Answer 1

You can certainly get the result with FOR XML EXPLICIT, but that's a PITA. Since FOR PATH doesn't allow separators in the path I guess that you're out of luck with that one, but maybe you could make something with an XQuery applied on the XML.

http://msdn.microsoft.com/en-us/library/ms189068.aspx

Answer 2

here's a hack:

declare @table table (Id int identity(1,1), Name varchar(50))


insert into @table values ('asdf')
insert into @table values ('fdsa')
insert into @table values ('fhgdf')
insert into @table values ('qewry')
insert into @table values ('sdtkjfhj')



select
(
select *
from @table
for XML PATH('Record'), ROOT('RecordParent'), type
) for xml path('root')