title: Select XML nodes as rows
body: <p>I am selecting from a table that has an XML column using T-SQL. I would like to select a certain type of node and have a row created for each one.</p>
  
  <p>For instance, suppose I am selecting from a <em>people</em> table. This table has an XML column for <em>addresses</em>. The XML is formated similar to the following:</p>
  
  <pre><code>&lt;address&gt;
    &lt;street&gt;Street 1&lt;/street&gt;
    &lt;city&gt;City 1&lt;/city&gt;
    &lt;state&gt;State 1&lt;/state&gt;
    &lt;zipcode&gt;Zip Code 1&lt;/zipcode&gt;
  &lt;/address&gt;
  &lt;address&gt;
    &lt;street&gt;Street 2&lt;/street&gt;
    &lt;city&gt;City 2&lt;/city&gt;
    &lt;state&gt;State 2&lt;/state&gt;
    &lt;zipcode&gt;Zip Code 2&lt;/zipcode&gt;
  &lt;/address&gt;
  </code></pre>
  
  <p>How can I get results like this:</p>
  
  <p><strong>Name</strong>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;<strong>City</strong>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;<strong>State</strong></p>
  
  <p>Joe Baker&nbsp;&nbsp;&nbsp;Seattle&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;WA</p>
  
  <p>Joe Baker&nbsp;&nbsp;&nbsp;Tacoma&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;WA</p>
  
  <p>Fred Jones&nbsp;&nbsp;Vancouver&nbsp;BC</p>