title: SQL Server split SELECT XML column as arbitrary individual columns
body: <p>In my application, I have few pre-defined fields for an object and user can define custom fields. I am using XML data type to store the custom fields in a name value format.</p>
  
  <p>e.g. I have Employees table that has FN, LN, Email as pre-defined columns and CustomFields as XML column to hold the user defined fields.</p>
  
  <p>And different rows can contain different custom fields. </p>
  
  <p>e.g. Row 1 -> John, Smith, jsmith@example.com, </p>
  
  <pre><code>&lt;root&gt;
      &lt;phone&gt;123-123-1234&lt;/phone&gt;
      &lt;country&gt;USA&lt;/country&gt;
  &lt;/root&gt;
  </code></pre>
  
  <p>and then Row 2 -> Smith, John, sjohn@example.com, </p>
  
  <pre><code>&lt;root&gt;
      &lt;age&gt;50&lt;/age&gt;
      &lt;sex&gt;Male&lt;/sex&gt;
  &lt;/root&gt;
  </code></pre>
  
  <p>And there can be any number of such custom fields defined for different employee records. The format will always be the same </p>
  
  <pre><code>&lt;root&gt;&lt;field&gt;value&lt;/field&gt;&lt;/root&gt;
  </code></pre>
  
  <p>How can I return Phone and Country as columns while selecting Row1 and return Age and Sex as columns while selecting Row2?</p>