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.

For instance, suppose I am selecting from a people table. This table has an XML column for addresses. The XML is formated similar to the following:

<address>
  <street>Street 1</street>
  <city>City 1</city>
  <state>State 1</state>
  <zipcode>Zip Code 1</zipcode>
</address>
<address>
  <street>Street 2</street>
  <city>City 2</city>
  <state>State 2</state>
  <zipcode>Zip Code 2</zipcode>
</address>

How can I get results like this:

Name         City         State

Joe Baker   Seattle      WA

Joe Baker   Tacoma     WA

Fred Jones  Vancouver BC