关键词:xml数据转为行列方式显示
常规案例:
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
触发器中的XML解析
【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