title: I need XML SQL query to get sum of nodes value based on another node value
body: <p>I am struggling with this all day and still do not have desired output. I need a query as in title. </p>
  
  <pre><code>&lt;aa&gt;
    &lt;bb&gt;
      &lt;cc&gt;
        &lt;typeid&gt;2&lt;/typeid&gt;
        &lt;dd&gt;
          &lt;amount&gt;10&lt;/amount&gt;
        &lt;/dd&gt;
      &lt;/cc&gt;
      &lt;cc&gt;
        &lt;typeid&gt;2&lt;/typeid&gt;
        &lt;dd&gt;
          &lt;amount&gt;20&lt;/amount&gt;
        &lt;/dd&gt;
      &lt;/cc&gt;
    &lt;/bb&gt;
  &lt;/aa&gt;
  </code></pre>
  
  <p>So my desire output would be 30, but it has to be based on the value type id, so pseudo code would be 
  select sum(node to amount) from XML where (typeid node) = 2</p>
  
  <p>I have tried:</p>
  
  <pre><code>SELECT t1.c1.value('sum(Amount)', 'float')
  FROM @xmlCase.nodes('//aa/bb/cc') as t(c)
  cross apply t.c.nodes('dd') as t1(c1)
  WHERE t.c.value('typeId[1]', 'int') = 2
  </code></pre>
  
  <p>But in this case I have received two values: 10 and 20, but I need sum of those values.
  Thank you.</p>