title: TSQL XML - Node attributes as columns or rows in same query
body: <p>I'm working with an XML file with a node similar to this:</p>
  <pre><code>&lt;Process&gt;    
      &lt;Step No=&quot;1&quot; Types=&quot;D&quot; Temp=&quot;25&quot; Secs=&quot;6&quot; Macro=&quot;2&quot;&gt;Enable Mixers&lt;/Step&gt;
      &lt;Step No=&quot;11&quot; Types=&quot;D1&quot; Temp=&quot;11&quot; Secs=&quot;61&quot; Macro=&quot;21&quot;&gt;Enable Mixers1&lt;/Step&gt;
      &lt;Step No=&quot;2&quot; Types=&quot;D2&quot; Temp=&quot;22&quot; Secs=&quot;62&quot; Macro=&quot;23&quot;&gt;Enable Mixers3&lt;/Step&gt;
  &lt;/Process&gt;
  </code></pre>
  <p>DDL:</p>
  <pre><code>DROP TABLE IF EXISTS MyXML2
  GO
  CREATE TABLE MyXML2(ID INT IDENTITY(1,1), c XML)
  INSERT MyXML2(c) VALUES 
      ('&lt;Process&gt;&lt;Step No=&quot;1&quot; Types=&quot;D1&quot; Temp=&quot;11&quot; Secs=&quot;61&quot; Macro=&quot;21&quot;&gt;Enable Mixers1&lt;/Step&gt;
       &lt;Step No=&quot;11&quot; Types=&quot;D1&quot; Temp=&quot;11&quot; Secs=&quot;61&quot; Macro=&quot;21&quot;&gt;Enable Mixers1&lt;/Step&gt;
       &lt;Step No=&quot;2&quot; Types=&quot;D2&quot; Temp=&quot;22&quot; Secs=&quot;62&quot; Macro=&quot;23&quot;&gt;Enable Mixers3&lt;/Step&gt;&lt;/Process&gt;')
  GO
  </code></pre>
  <p>I need to get a database structure like this (example given for only 1 &quot;Step&quot; above):</p>
  <div class="s-table-container">
  <table class="s-table">
  <thead>
  <tr>
  <th>StepNumber</th>
  <th>ColumnName</th>
  <th>ColumnValue</th>
  </tr>
  </thead>
  <tbody>
  <tr>
  <td>1</td>
  <td>Types</td>
  <td>D</td>
  </tr>
  <tr>
  <td>1</td>
  <td>Temp</td>
  <td>25</td>
  </tr>
  <tr>
  <td>1</td>
  <td>Secs</td>
  <td>6</td>
  </tr>
  <tr>
  <td>1</td>
  <td>Macro</td>
  <td>2</td>
  </tr>
  </tbody>
  </table>
  </div>
  <p><strong>My work so far:</strong> I've been able to map each attribute into a row.(Edit: updated to working example based on DDL above)</p>
  <pre><code>SELECT 
      col.value('local-name(.)', 'VARCHAR(50)') AS ColumnName,
      col.value('.[1]', 'VARCHAR(MAX)') AS ColumnValue
  FROM [MyXML2]
  CROSS APPLY [c].nodes('/Process/Step/@*') doc(col)
  </code></pre>
  <p>The output looks like:</p>
  <p><a href="https://i.stack.imgur.com/ZKLAb.png" rel="nofollow noreferrer"><img src="https://i.stack.imgur.com/ZKLAb.png" alt="" /></a></p>
  <p>But I need the &quot;No&quot; attribute to be a column.  Is there a way to do this all in one query?</p>
  <p>Fiddle: <a href="http://sqlfiddle.com/#!18/e56828/9" rel="nofollow noreferrer">http://sqlfiddle.com/#!18/e56828/9</a></p>