問題描述

我有一個 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

這篇關于SQL 服務器 xquery的文章就介紹到這了，希望我們推薦的答案對大家有所幫助，也希望大家多多支持html5模板網！