SQL xml列数据查询问题请教

shedawei 2018-10-19 10:31:30

表名: STUDENT_INFO
表列： SCHOOL (nvarchar(50)) , ST_INFO (xml)

ST_INFO 结构





<Students>

  <Student>

    <name>小红</name>

    <age>16</age>

    <address>北京市</address>

  </Student>

  <Student>

    <name>小明</name>

    <age>17</age>

    <address>上海市</address>

  </Student>

<Students>

怎么指定学校查询小红的年龄
比如：select age from STUDENT_INFO where name='小红' and SCHOOL='清华大学'

...全文

88 2 打赏收藏转发到动态举报

写回复

用AI写文章

2 条回复

切换为时间正序

请发表友善的回复…

发表回复

二月十六版主 2018-10-19

打赏
举报

--测试数据

if not object_id(N'STUDENT_INFO') is null

	drop table STUDENT_INFO

Go

Create table STUDENT_INFO([SCHOOL] NVARCHAR(50),ST_INFO XML)

Insert STUDENT_INFO

SELECT '清华大学' ,'<Students><Student><name>小红</name><age>16</age><address>北京市</address></Student><Student><name>小明</name><age>17</age><address>上海市</address></Student></Students>'

Go

--测试数据结束

Select age from STUDENT_INFO CROSS APPLY(

SELECT 

 doc.node.value('name[1]', 'varchar(50)') as name,

 doc.node.value('age[1]', 'varchar(50)') as age

FROM ST_INFO.nodes('/Students/Student') AS doc(node)

)t

 where name='小红' and SCHOOL='清华大学'