sql2008查询xml类型数据（包括对节点值的sum和节点属性的值的获取）

最新推荐文章于 2023-03-02 16:37:27 发布

moyan677

最新推荐文章于 2023-03-02 16:37:27 发布

阅读量685

点赞数

文章标签： XML

本文链接：https://blog.csdn.net/moyan677/article/details/83816715

版权

sql2008查询xml类型数据（包括对节点值的sum和节点属性的值的获取）

declare @x5 xml
set @x5=(select * FROM [tony].[dbo].[t_xml])
SELECT @x5.value('(/eventinfo/system/@user)[1]','varchar(20)') as [UserName],
@x5.value('(/eventinfo/system/@ip)[1]','varchar(20)') as [ServerIP],
@x5.value('(/eventinfo/events/event/data/project/@name)[1]','varchar(40)') as [ProjectName],

@x5.query('
sum(/eventinfo/events/event/data/reports/idpcireport/idp_ci_check/subcheck/topicNumber)
'
) as topicNumber,@x5.query('
sum(/eventinfo/events/event/data/reports/idpcireport/idp_ci_check/subcheck/fatalNumber)
'
) as fatalNumber,@x5.query('
sum(/eventinfo/events/event/data/reports/idpcireport/idp_ci_check/subcheck/errorNumber)
'
) as errorNumber,@x5.query('
sum(/eventinfo/events/event/data/reports/idpcireport/idp_ci_check/subcheck/warnNumber)
'
) as warnNumber,@x5.query('
sum(/eventinfo/events/event/data/reports/idpcireport/idp_ci_check/subcheck/infoNumber)
'
) as infoNumber,@x5.value('(/eventinfo/events/event/@time)[1]','datetime') as [dateTime]

-- ==================================================================
select
t_xml.value('(/eventinfo/system/@ip)[1]', 'nvarchar(20)') as ip,
t_xml.value('(/eventinfo/system/@user)[1]', 'nvarchar(20)') as [user],
t_xml.value('(/eventinfo/events/event/data/project/@name)[1]', 'nvarchar(50)') as project,
t_xml.value('sum(/eventinfo/events/event/data/reports/idpcireport/idp_ci_check/subcheck/topicNumber)', 'float') as topicNumber,
t_xml.value('sum(/eventinfo/events/event/data/reports/idpcireport/idp_ci_check/subcheck/fatalNumber)', 'float') as fatalNumber,
t_xml.value('sum(/eventinfo/events/event/data/reports/idpcireport/idp_ci_check/subcheck/errorNumber)', 'float') as errorNumber,
t_xml.value('sum(/eventinfo/events/event/data/reports/idpcireport/idp_ci_check/subcheck/warnNumber)', 'float') as warnNumber,
t_xml.value('sum(/eventinfo/events/event/data/reports/idpcireport/idp_ci_check/subcheck/infoNumber)', 'float') as infoNumber,
t_xml.value('count(/eventinfo/events/event/data/reports/idpcireport/idp_ci_check/subcheck/mapPath)', 'float') as totalMap,
t_xml.value('(/eventinfo/events/event/@time)[1]', 'datetime') as [datetime]
from dbo.t_xml