问题描述
我有一个 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