declare @data xml

declare @h int

set @data='

<bookstore>

<row>

<province_id></province_id>

<code></code>

<name>北京市</name>

</row>

<row>

<province_id></province_id>

<code></code>

<name>上海</name>

</row>

</bookstore>

'

exec sp_xml_preparedocument @h output,@data

select * from openxml(@h,'//row',)

with

(

province_id int,

code Varchar(),

name Varchar()

)

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