实战:sqlserver2008扩展事件-XML转换为标准的table格式(二)
'NVARCHAR(100)')
--获取hostname
SET @client_hostname = @xmlData.query('//action[@name="client_hostname"]/value').value('(value)[1]',
'NVARCHAR(100)')
--获取sql_text
SET @sql_text = @xmlData.query('//action[@name="sql_text"]/value').value('(value)[1]',
'NVARCHAR(MAX)')
--开始插入数据
INSERT #MyData
( database_id,
sql_text,
username,
client_hostname,
cpu )
VALUES ( @database_id, -- database_id - int
@sql_text, -- sql_text - nvarchar(max)
@username,
@client_hostname,
@cpu
)
END TRY
BEGIN CATCH
END CATCH
FETCH NEXT FROM myCur INTO @xmlString
END
CLOSE myCur
DEALLOCATE myCur
SELECT b.name,a.username,a.client_hostname,a.sql_text,a.cpu FROM #MyData AS a
inner join sys.databases as b
on a.database_id=b.database_id
order by a.cpu desc
go