Select XML nodes as rows
- by Bjørn
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
