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

moyan435

浏览: 35451 次
性别:
来自: 深圳

最近访客更多访客>>

zheyiw

bixucainiao

laoyouchen

yj5894878

博主相关

博客

微博

相册

留言

关于我

文章分类

社区版块

存档分类

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

XML

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

分享到：