sql - 使用 SQL + XQuery 将 XML 转换为基于列的数据

Question

我的 xml 类型列中有以下 xml(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>

不知道如何使用 SQL+XQuery 生成两列:

 p    w
---  ---
11   20
28   16
72   16
74   21
89   19
125  16

Answer 1

利用:

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