title: Count Elements with in XML Datatype
body: <p>Hi I have XML data stored in a table. The XML Data looks something like this.</p>
  
  <pre><code>&lt;Contacts&gt;
    &lt;Contact&gt;
      &lt;Name&gt;Evan Gabriel&lt;/Name&gt;
      &lt;PhoneNumbers&gt;
        &lt;PhoneNumber Type="Home" Number="555-1224" /&gt;
        &lt;PhoneNumber Type="Work" Number="578-9812" /&gt;
        &lt;PhoneNumber Type="Cell" Number="578-7299" /&gt;
      &lt;/PhoneNumbers&gt;
    &lt;/Contact&gt;
    &lt;Contact&gt;
      &lt;Name&gt;Elizabeth Dallas&lt;/Name&gt;
      &lt;PhoneNumbers&gt;
        &lt;PhoneNumber Type="Home" Number="714-5488" /&gt;
        &lt;PhoneNumber Type="Cell" Number="714-1099" /&gt;
      &lt;/PhoneNumbers&gt;
    &lt;/Contact&gt;
    &lt;Contact&gt;
      &lt;Name&gt;Anna Palmer&lt;/Name&gt;
      &lt;PhoneNumbers&gt;
        &lt;PhoneNumber Type="Work" Number="555-1440" /&gt;
        &lt;PhoneNumber Type="Cell" Number="714-0523" /&gt;
      &lt;/PhoneNumbers&gt;
    &lt;/Contact&gt;
  &lt;/Contacts&gt;
  </code></pre>
  
  <p>What I am trying to do is to get The count of contact Numbers for each Client and I want the retuned output to be in XML datatype. </p>
  
  <p>My Desired OUTPUT is as follows:</p>
  
  <pre><code>&lt;Contacts&gt;
    &lt;Contact&gt;
      &lt;Name&gt;Elizabeth Dallas&lt;/Name&gt;
      &lt;NumFound&gt;2&lt;/NumFound&gt;
    &lt;/Contact&gt;
    &lt;Contact&gt;
      &lt;Name&gt;Anna Palmer&lt;/Name&gt;
      &lt;NumFound&gt;2&lt;/NumFound&gt;
    &lt;/Contact&gt;
    &lt;Contact&gt;
      &lt;Name&gt;Evan Gabriel&lt;/Name&gt;
      &lt;NumFound&gt;3&lt;/NumFound&gt;
    &lt;/Contact&gt;
  &lt;/Contacts&gt; 
  </code></pre>
  
  <p>Following is my attempt: </p>
  
  <pre><code>SELECT
      COUNT(*) AS NumCount
  FROM
     (SELECT Xml_DATA FROM Xml_Table) AS XmlTable(XmlColumn)
  CROSS APPLY 
     XmlColumn.nodes('/Contacts/Contact/Name/PhoneNumbers/PhoneNumber') XmlTableFunction(XmlColumn2)
  </code></pre>
  
  <p>But I dont think I am anywhere near the actual solution. Any help or pointer in the right direction is much appreciated. </p>
  
  <p>Thank you in Advance.</p>
  
  <h2><a href="http://sqlfiddle.com/#!3/33472/2" rel="nofollow"><code>SQL FIDDLE with Test Data and My Attempt</code></a></h2>