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)

AakashM · Accepted Answer · 2010-07-30 13:34:17Z

3

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

answered Jul 30, 2010 at 13:34

AakashM

63.5k17 gold badges154 silver badges190 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

grenade Over a year ago

Thanks! That's the missing bits.

Collectives™ on Stack Overflow

Pivot XML using XQuery and filter on attribute

1 Answer 1

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related