How to return SQL Server 2005/2008 columns as identical child nodes using FOR XML query?

Question

Basically I need to return some data from a SQL Server table in the following XML format:

<querydata>
  <entity name="Person.Contact">
    <row>
      <field name="FirstName">Gustavo</field>
      <field name="LastName">Achong</field>
    </row>
    <row>
      <field name="FirstName">Catherine</field>
      <field name="LastName">Abel</field>
    </row>
...
  </entity>
</querydata>

I have come up with the following SQL statement:

select 'Person.Contact' as "@name", 
(select FirstName, LastName from Person.Contact for XML path('row'), TYPE)
for XML path('entity'), root('querydata')

Which produces this output:

<querydata>
  <entity name="Person.Contact">
    <row>
      <FirstName>Gustavo</FirstName>
      <LastName>Achong</LastName>
    </row>
    <row>
      <FirstName>Catherine</FirstName>
      <LastName>Abel</LastName>
    </row>
....
  </entity>
</querydata>

But I have gotten no further. Thanks!

Answer 1

You need to unpivot your data.

Try using a subquery along the lines of:

SELECT 'FirstName' as [@name], FirstName as [*]
union all
SELECT 'LastName' as [@name], LastName as [*]
for xml path('field')

Or something along these lines...

I don't have SQL with me (on my iPhone today), but I'm thinking about:

select 'Person.Contact' as "@name", 
(select (SELECT 'FirstName' as [@name], FirstName as [*]
union all
SELECT 'LastName' as [@name], LastName as [*]
for xml path('field')) from Person.Contact for XML path('row'), TYPE)
for XML path('entity'), root('querydata')

Answer 2

Thank you very much Rob! You definitely got me on the right track, +1 for you! I had to wrap everything in a SELECT * FROM statement, otherwise SQL server complains. Here is the final working query:

SELECT 'Person.Contact' as "@name",
(SELECT 
    (SELECT * from (SELECT 'FirstName' as [@name], [FirstName] as [*]
    union all
    SELECT 'LastName' as [@name], [LastName] as [*]) y
    for xml path('field'), TYPE)
from Person.Contact for XML path, TYPE)
for XML path('entity'), root('querydata')