title: Sql Server table to XML with multiple child nodes
body: <p>I have the following table</p>
  
  <pre><code>name  | age | misc
  ------------------
  david | 20  | foo
  john  | 30  | bar
  </code></pre>
  
  <p>And I want to transform it into the following XML:</p>
  
  <pre><code>&lt;doc&gt;
    &lt;field name="name" val="david" /&gt;
    &lt;field name="age" val="20" /&gt;
    &lt;field name="misc" val="foo" /&gt;
  &lt;/doc&gt;
  &lt;doc&gt;
    &lt;field name="name" val="john" /&gt;
    &lt;field name="age" val="30" /&gt;
    &lt;field name="misc" val="bar" /&gt;
  &lt;/doc&gt;
  </code></pre>
  
  <p>I have for this working below for a single column, however if I try to add a second column for another <code>field</code> node I get the error:</p>
  
  <pre><code>Msg 9303, Level 16, State 1, Line 25
  XQuery [query()]: Syntax error near 'name', expected '}'.
  </code></pre>
  
  <p>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.</p>
  
  <p>Any assistance is appreciated!</p>
  
  <pre><code>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 
     &lt;doc&gt;{
       &lt;field name="name" val="{data($d/@name)}"  /&gt;
    }&lt;/doc&gt;'
  )
  
  /*This one is what I want*/
  SELECT (select * from @MyData as MyData for xml auto, type).query
  (
  ' for $d in /MyData
     return 
     &lt;doc&gt;{
       &lt;field name="name" val="{data($d/@name)}"  /&gt;
       &lt;field name="age" val="{data($d/@age)}"  /&gt;
       &lt;field name="misc" val="{data($d/@misc)}"  /&gt;
    }&lt;/doc&gt;'
  )
  </code></pre>