Xml to column based data using SQL + XQuery

Question

I have the following xml in my xml-typed column (xml itself is un-typed)

<wi>
  <w wid="16">
    <p>28</p>
    <p>72</p>
    <p>125</p>
  </w>
  <w wid="19">
    <p>89</p>
  </w>
  <w wid="20">
    <p>11</p>
  </w>
  <w wid="21">
    <p>74</p>
  </w>
</wi>

Can't figure out how to produce two-columns using SQL+XQuery:

Kirill Polishchuk · Accepted Answer · 2012-07-22 02:39:21Z

2

Use:

declare @x xml = '<wi>
  <w wid="16">
    <p>28</p>
    <p>72</p>
    <p>125</p>
  </w>
  <w wid="19">
    <p>89</p>
  </w>
  <w wid="20">
    <p>11</p>
  </w>
  <w wid="21">
    <p>74</p>
  </w>
</wi>'

select *
from
(
    select t.c.value('.', 'int') p
        , t.c.value('../@wid', 'int') w
    from @x.nodes('//p') t(c)
)t
order by p, w

answered Jul 22, 2012 at 2:39

Kirill Polishchuk

56.4k11 gold badges125 silver badges127 bronze badges

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

2 Comments

Yuriy Gettya Over a year ago

Assuming I added namespace to the root node: <wi xmlns="example.com">, how do I specify it in the value() call? no syntax I try works.

Yuriy Gettya Over a year ago

I guess a better question would be - assuming I have a schema, how do I specify that schema in the value() call? I already added "with xmlnamespaces(default 'example.com')" to the sql query

Kirill Polishchuk · Accepted Answer · 2012-07-22 20:25:18Z

2

Or another approach using cross apply:

select *
from
(
    select a.b.value('.', 'int') p
        , t.c.value('@wid', 'int') w
    from @x.nodes('//w') t(c)
    cross apply t.c.nodes('p') a(b)
)t
order by p, w

answered Jul 22, 2012 at 20:25

Kirill Polishchuk

56.4k11 gold badges125 silver badges127 bronze badges

1 Comment

Mikael Eriksson Over a year ago

You will have better performance not using the parent axis ... +1 for this version.

Collectives™ on Stack Overflow

Xml to column based data using SQL + XQuery

2 Answers 2

2 Comments

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

2 Comments

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related