（4.31）sql server中的xml数据操作

<event name="sql_batch_completed" package="sqlserver" timestamp="2020-08-19T06:41:42.542Z">
  <data name="cpu_time">
    <value>141000</value>
  </data>
  <data name="duration">
    <value>2947856</value>
  </data>
  <data name="physical_reads">
    <value>12517</value>
  </data>
  <data name="logical_reads">
    <value>10411</value>
  </data>
  <data name="writes">
    <value>0</value>
  </data>
  <data name="row_count">
    <value>0</value>
  </data>
  <data name="result">
    <value>2</value>
    <text>Abort</text>
  </data>
  <data name="batch_text">
    <value>SELECT  * FROM match_nndouble </value>
  </data>
  <action name="task_time" package="sqlos">
    <value>28573288</value>
  </action>
  <action name="database_name" package="sqlserver">
    <value>test</value>
  </action>
  <action name="nt_username" package="sqlserver">
    <value>WIN-OFM2A36CRMD\admin</value>
  </action>
  <action name="sql_text" package="sqlserver">
    <value>SELECT  * FROM match_nndouble </value>
  </action>
  <action name="transaction_id" package="sqlserver">
    <value>0</value>
  </action>
  <action name="username" package="sqlserver">
    <value>WIN-OFM2A36CRMD\admin</value>
  </action>
</event>

with d as (
SELECT CONVERT(XML,event_data) AS data
from sys.Fn_xe_file_target_read_file(N'E:\dba_tools\eventlog\slow_query_0_132422809173040000.xel',NULL,NULL,NULL)
)
select 
data.value('(/event/@name)[1]','nvarchar(128)') as operation_name, --获取最上方标题行的内容
--data.value('(/event/data[@name="cpu_time"]/value)[1]','int')/1000 as 'cpu_time(ms)',--获得 event=>data name=cpu_time 的 value
data.value('(/event/data[@name="duration"]/value)[1]','int')/1000 as 'exec_time(ms)',--获得 event=>data name=duration 的 value
--data.value('(/event/data[@name="physical_reads"]/value)[1]','int') as 'physical_reads',--获得 event=>data name=physical_reads 的 value
--data.value('(/event/data[@name="logical_reads"]/value)[1]','int') as 'logical_reads',--获得 event=>data name=logical_reads 的 value
--data.value('(/event/data[@name="writes"]/value)[1]','int') as 'writes',--获得 event=>data name=writes 的 value
data.value('(/event/data[@name="row_count"]/value)[1]','int') as 'row_count',--获得 event=>data name=row_count 的 value
data.value('(/event/data[@name="result"]/value)[1]','int') as 'result_flag',--获得 event=>data name=result 的 value
data.value('(/event/data[@name="result"]/text)[1]','nvarchar(128)') as 'result_desc',--获得 event=>data name=result 的 text
data.value('(/event/data[@name="batch_text"]/value)[1]','nvarchar(max)') as 'batch_text',--获得 event=>data name=batch_text 的 text

data.value('(/event/action[@name="sql_text"]/value)[1]','nvarchar(4000)') as 'current_sql',--获得 event=>action name=sql_text 的 value
--data.value('(/event/action[@name="task_time"]/value)[1]','int')/1000 as 'task_time(ms)',--获得 event=>action name=task_time 的 value
data.value('(/event/action[@name="database_name"]/value)[1]','nvarchar(400)') as 'database_name',--获得 event=>action name=database_name 的 value
data.value('(/event/action[@name="transaction_id"]/value)[1]','nvarchar(400)') as 'transaction_id',--获得 event=>action name=transaction_id 的 value
data.value('(/event/action[@name="username"]/value)[1]','nvarchar(400)') as 'username',--获得 event=>action name=transaction_id 的 value
data.value('(/event/action[@name="nt_username"]/value)[1]','nvarchar(400)') as 'nt_username'--获得 event=>action name=transaction_id 的 value
from d

declare @data xml
declare @h int
set @data='
<bookstore>
<row>
<province_id>0</province_id>
<code>11</code>
<name>北京市</name>
</row>
<row>
<province_id>1</province_id>
<code>22</code>
<name>上海</name>
</row>
</bookstore>
'

exec sp_xml_preparedocument @h output,@data
select * from openxml(@h,'//row',2)
with
(
province_id int,
code Varchar(100),
name Varchar(100)
) 
exec sp_xml_removedocument @h

【1】数据库级别DDL操作监控审计

SQL Server 2005开始支持DDL触发器，它不只限于对CREATE/ALTER/DROP操作有效，支持的DDL事件还有比如：权限的GRANT/DENY/REVOEK, 对象的RENAME, 更新统计信息等等，可通过DMV查看更多支持的事件类型如下：

select * from sys.trigger_event_types
where type_name not like '%CREATE%'
  and type_name not like '%ALTER%'
  and type_name not like '%DROP%'
注意：

1. TRUNCATE不在DDL触发器的事件类型中，SQL Server中将Truncate 归为DML操作语句，虽然它也并不触发DML触发器，就像开启开关的大批量导入操作 (Bulk Import Operations) 一样；

2. DDL触发器中捕获的信息都由EVENTDATA()函数返回，返回类型为XML格式，需要用XQuery来读取；

 

案例：转自2012示例库，只能数据库级别，不能实例级别

复制代码
use database
go

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
create table databaseLog( [PostTime] datetime,
[DatabaseUser] varchar(500),
[Event] varchar(500),
[Schema] varchar(50),
[Object] varchar(4000),
[TSQL] varchar(4000),
[XmlEvent] xml)

 
CREATE TRIGGER [ddlDatabaseTriggerLog] ON DATABASE --all server 实例级别
FOR DDL_DATABASE_LEVEL_EVENTS AS　　--DDL_SERVER_LEVEL_EVENTS 实例级别
BEGIN
    SET NOCOUNT ON;
 
    DECLARE @data XML;
    DECLARE @schema sysname;
    DECLARE @object sysname;
    DECLARE @eventType sysname;
 
    SET @data = EVENTDATA();
    SET @eventType = @data.value('(/EVENT_INSTANCE/EventType)[1]', 'sysname');
    SET @schema = @data.value('(/EVENT_INSTANCE/SchemaName)[1]', 'sysname');
    SET @object = @data.value('(/EVENT_INSTANCE/ObjectName)[1]', 'sysname')
 
    IF @object IS NOT NULL
        PRINT '  ' + @eventType + ' - ' + @schema + '.' + @object;
    ELSE
        PRINT '  ' + @eventType + ' - ' + @schema;
 
    IF @eventType IS NULL
        PRINT CONVERT(nvarchar(max), @data);
 
    INSERT [dbo].[DatabaseLog]
        (
        [PostTime],
        [DatabaseUser],
        [Event],
        [Schema],
        [Object],
        [TSQL],
        [XmlEvent]
        )
    VALUES
        (
        GETDATE(),
        CONVERT(sysname, CURRENT_USER),
        @eventType,
        CONVERT(sysname, @schema),
        CONVERT(sysname, @object),
        @data.value('(/EVENT_INSTANCE/TSQLCommand)[1]', 'nvarchar(max)'),
        @data
        );
END;
 
GO
 
SET ANSI_NULLS OFF
GO
 
SET QUOTED_IDENTIFIER OFF
GO

--开启/关闭 
ENABLE TRIGGER [ddlDatabaseTriggerLog] ON DATABASE
DISABLE TRIGGER [ddlDatabaseTriggerLog] ON DATABASE
GO

--删除
DROP TRIGGER tri_LogServerEvent ON DATABASE;

--添加扩展属性到数据库对象中（即添加数据字典注解）
EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'Database trigger to audit all of the DDL changes made to the AdventureWorks2008R2 database.' , 
@level0type=N'TRIGGER',@level0name=N'ddlDatabaseTriggerLog' GO