title: Select Multiple Columns from XML Type column in MS SQL Server
body: <p>I have used XML column for store data which can have dynamic nodes and attributes i.e. a Account node can have N number of nodes, I would like to retrieve those all nodes in single select query as multiple columns i.e. Col1, Col2, Col3 etc.</p>
  
  <p>I tried below query but its returning vertical rows, I would like to have horizontal rows. Below is expected output. Col1, Col2 ..Nth Col</p>
  
  <pre><code>TAX-1 Amount|TAX-2 Amount|TAX-3 Amount...Nth Columns
  1232        |4050        |57   
  </code></pre>
  
  <p>Sample Data</p>
  
  <pre><code>DECLARE @XML XML
  SET @XML='&lt;Info&gt;
     &lt;Demand&gt;
        &lt;Tax ID="1"&gt;
           &lt;Head&gt;1232&lt;/Head&gt;
           &lt;Int&gt;10&lt;/Int&gt;
           &lt;OtherFee&gt;5&lt;/OtherFee&gt;        
        &lt;/Tax&gt;
        &lt;Tax ID="2"&gt;
           &lt;Head&gt;4050&lt;/Head&gt;
           &lt;Int&gt;10&lt;/Int&gt;
           &lt;OtherFee&gt;5&lt;/OtherFee&gt;         
        &lt;/Tax&gt;
         &lt;Tax ID="3"&gt;
           &lt;Head&gt;57&lt;/Head&gt;
           &lt;Int&gt;10&lt;/Int&gt;
           &lt;OtherFee&gt;5&lt;/OtherFee&gt;         
        &lt;/Tax&gt;
      &lt;/Demand&gt;
     &lt;Collection&gt;      
        &lt;Tax ID="1"&gt;
           &lt;Head&gt;500&lt;/Head&gt;
           &lt;Int&gt;10&lt;/Int&gt;
           &lt;OtherFee&gt;5&lt;/OtherFee&gt;         
        &lt;/Tax&gt;
        &lt;Tax ID="2"&gt;
           &lt;Head&gt;800&lt;/Head&gt;
           &lt;Int&gt;10&lt;/Int&gt;
           &lt;OtherFee&gt;5&lt;/OtherFee&gt;         
        &lt;/Tax&gt;
     &lt;/Collection&gt;   
  &lt;/Info&gt;'
  </code></pre>
  
  <p>Current query which is returning vertical output</p>
  
  <pre><code>SELECT T.C.value('Head[1]', 'varchar(max)') as Amount,
  T.C.value('@ID', 'varchar(max)') as Head
  FROM @XML.nodes('/Info/Demand/Tax') as T(C)
  </code></pre>
  
  <p>See <a href="http://sqlfiddle.com/#!3/d41d8/32914" rel="nofollow">SQL Fiddle with Demo</a></p>
  
  <p>Any suggestion or input are most welcome!</p>
  
  <p>Thanks
  Suresh</p>