title: Using SQL to transpose/flatten XML structure to columns
body: <p>I am using SQL Server (2008/2012) and I know there are similar answers from lots of searching, however I can't seem to find the appropriate example/pointers for my case.</p>
  
  <p>I have an XML column in a SQL Server table holding this data:</p>
  
  <pre><code>&lt;Items&gt;
   &lt;Item&gt;
    &lt;FormItem&gt;
      &lt;Text&gt;FirstName&lt;/Text&gt;
      &lt;Value&gt;My First Name&lt;/Value&gt;
    &lt;/FormItem&gt;
    &lt;FormItem&gt;
      &lt;Text&gt;LastName&lt;/Text&gt;
      &lt;Value&gt;My Last Name&lt;/Value&gt;
    &lt;/FormItem&gt;
    &lt;FormItem&gt;
      &lt;Text&gt;Age&lt;/Text&gt;
      &lt;Value&gt;39&lt;/Value&gt;
    &lt;/FormItem&gt;
   &lt;/Item&gt;
   &lt;Item&gt;
    &lt;FormItem&gt;
      &lt;Text&gt;FirstName&lt;/Text&gt;
      &lt;Value&gt;My First Name 2&lt;/Value&gt;
    &lt;/FormItem&gt;
    &lt;FormItem&gt;
      &lt;Text&gt;LastName&lt;/Text&gt;
      &lt;Value&gt;My Last Name 2&lt;/Value&gt;
    &lt;/FormItem&gt;
    &lt;FormItem&gt;
      &lt;Text&gt;Age&lt;/Text&gt;
      &lt;Value&gt;40&lt;/Value&gt;
    &lt;/FormItem&gt;
   &lt;/Item&gt;
  &lt;/Items&gt;
  </code></pre>
  
  <p>So even though the structure of <code>&lt;FormItem&gt;</code> is going to be the same, I can have multiple (most commonly no more than 20-30) sets of form items.. </p>
  
  <p>I am essentially trying to return a query from SQL in the format below, i.e. dynamic columns based on /FormItem/Text:</p>
  
  <pre><code>FirstName         LastName         Age    ---&gt; More columns as new `&lt;FormItem&gt;` are returned
  My First Name     My Last Name     39          Whatever value etc..
  My First Name 2   My Last Name 2   40          
  </code></pre>
  
  <p>So, at the moment I had the following:</p>
  
  <pre><code>select 
      Tab.Col.value('Text[1]','nvarchar(100)') as Question,
      Tab.Col.value('Value[1]','nvarchar(100)') as Answer
  from
      @Questions.nodes('/Items/Item/FormItem') Tab(Col)
  </code></pre>
  
  <p>Of course that hasn't transposed my XML rows into columns, and obviously is fixed with fields anyway.. I have been trying various "Dynamic SQL" approaches where the SQL performs a distinct selection of (in my case) the <code>&lt;Text&gt;</code> node, and then uses some sort of Pivot? but I couldn't seem to find the magic combination to return the results I need as a dynamic set of columns for each row (<code>&lt;Item&gt;</code> within the collection of <code>&lt;Items&gt;</code>).</p>
  
  <p>I'm sure it can be done having seen so many very similar examples, however again the solution eludes me!</p>
  
  <p>Any help gratefully received!!</p>