Sql Server table to XML with multiple child nodes

Question

I have the following table

name  | age | misc
------------------
david | 20  | foo
john  | 30  | bar

And I want to transform it into the following XML:

<doc>
  <field name="name" val="david" />
  <field name="age" val="20" />
  <field name="misc" val="foo" />
</doc>
<doc>
  <field name="name" val="john" />
  <field name="age" val="30" />
  <field name="misc" val="bar" />
</doc>

I have for this working below for a single column, however if I try to add a second column for another field node I get the error:

Msg 9303, Level 16, State 1, Line 25
XQuery [query()]: Syntax error near 'name', expected '}'.

This is a sample of what I am trying to do and is ready to run in SQL Server Management Studio. I can't find much documentation on the syntax and am quite lost for ideas.

Any assistance is appreciated!

declare @MyData table (name varchar(200), age varchar(200), misc varchar(200))

insert into @MyData values('david', '20', 'foo')
insert into @MyData values('john', '30', 'bar')

/*This one works fine*/
SELECT (select * from @MyData as MyData for xml auto, type).query
(
' for $d in /MyData
   return 
   <doc>{
     <field name="name" val="{data($d/@name)}"  />
  }</doc>'
)

/*This one is what I want*/
SELECT (select * from @MyData as MyData for xml auto, type).query
(
' for $d in /MyData
   return 
   <doc>{
     <field name="name" val="{data($d/@name)}"  />
     <field name="age" val="{data($d/@age)}"  />
     <field name="misc" val="{data($d/@misc)}"  />
  }</doc>'
)

Answer 1

How about this..

select 
    (select 'name' as 'field/@name', a.name as 'field/@val' for xml path(''), type),
    (select 'age' as 'field/@name', a.age as 'field/@val' for xml path(''), type),
    (select 'misc' as 'field/@name', a.misc as 'field/@val' for xml path(''), type)
from 
    MyData a for xml path('doc')

for your XQuery version try this: (I just removed the curly braces) Is that OK?

SELECT (select * from @MyData as MyData for xml auto, type).query
(
' for $d in /MyData
   return 
   <doc>
     <field name="name" val="{data($d/@name)}"  />
     <field name="age"  val="{data($d/@age)}"  />
     <field name="misc" val="{data($d/@misc)}"  />
  </doc>'
)