title: How do I use column values as xml element names using for xml in SQL Server 2005?
body: <p>Simplifying what I'm doing somewhat, as an example, say I have the following tables:</p>
  
  <pre><code>declare @elements table (id int, name nvarchar(20))
  
  insert into @elements (id, name) values (1, 'FirstName')
  insert into @elements (id, name) values (2, 'Surname')
  insert into @elements (id, name) values (3, 'Address')
  
  declare @values table (id int, value nvarchar(20), elementId int)
  
  insert into @values (id, value, elementId) values (1, 'XXX', 1)
  insert into @values (id, value, elementId) values (2, 'YYY', 2)
  insert into @values (id, value, elementId) values (3, 'ZZZ', 3)
  </code></pre>
  
  <p>which simply defines a table of element names that could be dynamic, against which are defined a table of values.</p>
  
  <p>What I would like is to generate XML in the following form, where the values of the @elements table become the element names, and the values of the @values table become the values.</p>
  
  <pre><code>&lt;Customer&gt;
      &lt;FirstName&gt;XXX&lt;/FirstName&gt;
      &lt;Surname&gt;YYY&lt;/Surname&gt;
      &lt;Address&gt;ZZZ&lt;Address&gt;
  &lt;/Customer&gt;
  </code></pre>
  
  <p>However my efforts with <code>for xml</code> so far are not going so well:</p>
  
  <pre><code>select e.name, v.value from @elements e
  inner join @values v on v.elementId = e.id
  for xml path(''), root('customer')
  </code></pre>
  
  <p>returns</p>
  
  <pre><code>&lt;customer&gt;
    &lt;name&gt;FirstName&lt;/name&gt;
    &lt;value&gt;XXX&lt;/value&gt;
    &lt;name&gt;Surname&lt;/name&gt;
    &lt;value&gt;YYY&lt;/value&gt;
    &lt;name&gt;Address&lt;/name&gt;
    &lt;value&gt;ZZZ&lt;/value&gt;
  &lt;/customer&gt;
  </code></pre>
  
  <p><code>for xml auto</code> returns</p>
  
  <pre><code>&lt;customer&gt;
    &lt;e name="FirstName"&gt;
      &lt;v value="XXX" /&gt;
    &lt;/e&gt;
    &lt;e name="Surname"&gt;
      &lt;v value="YYY" /&gt;
    &lt;/e&gt;
    &lt;e name="Address"&gt;
      &lt;v value="ZZZ" /&gt;
    &lt;/e&gt;
  &lt;/customer&gt;
  </code></pre>
  
  <p><code>for xml raw</code> returns</p>
  
  <pre><code>&lt;customer&gt;
    &lt;row name="FirstName" value="XXX" /&gt;
    &lt;row name="Surname" value="YYY" /&gt;
    &lt;row name="Address" value="ZZZ" /&gt;
  &lt;/customer&gt;
  </code></pre>
  
  <p>Is there a way I can get the values from a column to output as element names? I'm sure I'm missing something obviously simple here.</p>