SQL 服务器 xquery

问题描述

我有一个 xml 数据

I have a xml data

<Education>

          <School ID="1">
          <Degree>test</Degree>
          <Description>desc1</Description>
          <Institution>kec</Institution>
          </School>

          <School ID="2">
          <Degree>test2</Degree>
          <Description>desc2</Description>
          <Institution>kec2</Institution>
             </School>

      </Education>

现在我需要将学校的详细信息提取到一些临时表中

now I need to extract the School detail into some temp table

喜欢

----------------------------------
schoolid degree desc instituion
----------------------------------
1        test    desc1  kec
2        test2   desc2  kec2

谁能帮帮我

推荐答案

使用:

declare @x xml='<Education>

          <School ID="1">
          <Degree>test</Degree>
          <Description>desc1</Description>
          <Institution>kec</Institution>
          </School>

          <School ID="2">
          <Degree>test2</Degree>
          <Description>desc2</Description>
          <Institution>kec2</Institution>
             </School>

      </Education>'


select t.s.value('@ID', 'int') [schoolid]
    , t.s.value('Degree[1]', 'nvarchar(20)') [degree]
    , t.s.value('Description[1]', 'nvarchar(20)') [desc]
    , t.s.value('Institution[1]', 'nvarchar(20)') [instituion]
from @x.nodes('Education/School') t(s)

输出:

schoolid    degree               desc                 instituion
----------- -------------------- -------------------- --------------------
1           test                 desc1                kec
2           test2                desc2                kec2