title: Convert XML column to Table Data in MS SQL Query.
body: <p>In a table in my data base I have a column in which the entire entry is a long XML String with the following structure:</p>
  
  <pre><code>  &lt;Group1&gt;
        &lt;Title&gt;
            &lt;Name&gt;John Doe&lt;/Name&gt;
            &lt;Phone&gt;555-3421&lt;/Phone&gt;
            &lt;Email&gt;catman@gmail.com&lt;/Email&gt;
            (+more)
        &lt;/Title&gt;
    &lt;/Group1&gt;
  </code></pre>
  
  <p>This is my SELECT Statement and here is what it outputs:</p>
  
  <pre><code>SELECT TheKey, TheData FROM MyTable;
  </code></pre>
  
  <p>Output in SQL Server Management Studio:</p>
  
  <pre><code>TheKey  TheData
  10000   &lt;Group1&gt;&lt;Title&gt;&lt;Name&gt;John Doe&lt;/Name&gt;&lt;Phone&gt;893-3421&lt;/Phone&gt;&lt;Email&gt;catman@gmail.com&lt;/Email&gt;&lt;/Title&gt;&lt;/Group1&gt;
  10001   &lt;Group1&gt;&lt;Title&gt;&lt;Name&gt;Mary Sue&lt;/Name&gt;&lt;Phone&gt;381-2342&lt;/Phone&gt;&lt;Email&gt;thebestdude@gmail.com&lt;/Email&gt;&lt;/Title&gt;&lt;/Group1&gt;
  10002   &lt;Group1&gt;&lt;Title&gt;&lt;Name&gt;Mark Dark&lt;/Name&gt;&lt;Phone&gt;312-7626&lt;/Phone&gt;&lt;Email&gt;mybook231@gmail.com&lt;/Email&gt;&lt;/Title&gt;&lt;/Group1&gt;
  10003   &lt;Group1&gt;&lt;Title&gt;&lt;Name&gt;Garth Dan&lt;/Name&gt;&lt;Phone&gt;341-4572&lt;/Phone&gt;&lt;Email&gt;lampshade032@gmail.com&lt;/Email&gt;&lt;State&gt;California&lt;/State&gt;&lt;/Title&gt;&lt;/Group1&gt;
  </code></pre>
  
  <p>I would like to write some sort of MS SQL query that will return the data like this to me:</p>
  
  <pre><code>TheKey  Name        Phone       Email                          State
  10000   John Doe    893-3421    catman@gmail.com                NULL
  10001   Mary Sue    381-2342    thebestdude@gmail.com           NULL
  10002   Mark Dark   312-7626    mybook231@gmail.com             NULL
  10003   Garth Dan   341-4572    lampshade032@gmail.com         California
  </code></pre>
  
  <p><strong>Notice</strong> how the last entry had an extra XML tag <code>&lt;State&gt;</code> which the other entries did not. I would like it to be flexible like this - using some sort of parent/child references? </p>
  
  <p>Any help doing this would be greatly appreciated, I just can't seem to find anything like it anywhere =)</p>