Pivot XML using XQuery and filter on attribute

Question

Given the following XML (in an SQL column field called 'xfield'):

<data>
  <section>
    <item id="A">
      <number>987</number>
    </item>
    <item id="B">
      <number>654</number>
    </item>
    <item id="C">
      <number>321</number>
    </item>
  </section>
  <section>
    <item id="A">
      <number>123</number>
    </item>
    <item id="B">
      <number>456</number>
    </item>
    <item id="C">
      <number>789</number>
    </item>
  </section>
</data>

How do you obtain the following table structure (with A, B & C as the column names):

 A | B | C
987|654|321
123|456|789

Using SQL XQuery, I'm trying this (not surprisingly, it's invalid):

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)

Answer 1

You're nearly there.

You need to use nodes() to shred the xml into the rows you want to work with - here, you want a resultset row for each section element, so shred with

nodes('/data/section')

Once you've done that, you just need to make your xpath [1] syntactically correct (and relative to the section nodes you will be 'in'):

data.value('(item[@id = "A"]/number)[1]', 'int') as A,
data.value('(item[@id = "B"]/number)[1]', 'int') as B,
data.value('(item[@id = "C"]/number)[1]', 'int') as C

And voila:

A           B           C
----------- ----------- -----------
987         654         321
123         456         789