title: Pivot XML using XQuery and filter on attribute
body: <p>Given the following XML (in an SQL column field called 'xfield'):</p>
  
  <pre><code>&lt;data&gt;
    &lt;section&gt;
      &lt;item id="A"&gt;
        &lt;number&gt;987&lt;/number&gt;
      &lt;/item&gt;
      &lt;item id="B"&gt;
        &lt;number&gt;654&lt;/number&gt;
      &lt;/item&gt;
      &lt;item id="C"&gt;
        &lt;number&gt;321&lt;/number&gt;
      &lt;/item&gt;
    &lt;/section&gt;
    &lt;section&gt;
      &lt;item id="A"&gt;
        &lt;number&gt;123&lt;/number&gt;
      &lt;/item&gt;
      &lt;item id="B"&gt;
        &lt;number&gt;456&lt;/number&gt;
      &lt;/item&gt;
      &lt;item id="C"&gt;
        &lt;number&gt;789&lt;/number&gt;
      &lt;/item&gt;
    &lt;/section&gt;
  &lt;/data&gt;
  </code></pre>
  
  <p>How do you obtain the following table structure (with A, B &amp; C as the column names):</p>
  
  <pre><code> A | B | C
  987|654|321
  123|456|789
  </code></pre>
  
  <p>Using SQL XQuery, I'm trying this (not surprisingly, it's invalid):</p>
  
  <pre><code>SELECT
    data.value('(./section/item[@ID = "A"]/number/[1])', 'int') as A,
    data.value('(./section/item[@ID = "B"]/number/[1])', 'int') as B,
    data.value('(./section/item[@ID = "C"]/number/[1])', 'int') as C
  FROM Table CROSS APPLY [xfield].nodes('/data') t(data)
  </code></pre>