34,576
社区成员
发帖
与我相关
我的任务
分享
<Students>
<Student>
<name>小红</name>
<age>16</age>
<address>北京市</address>
</Student>
<Student>
<name>小明</name>
<age>17</age>
<address>上海市</address>
</Student>
<Students>
--测试数据
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='清华大学'