• (4.31)sql server中的xml数据操作


    关键词: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
  • 相关阅读:
    JavaSript数组扁平化去重
    宝塔面板忘记登陆账号和密码怎么办
    宝塔shell脚本执行thinkphp命令行
    laravel设置中国时区
    Laravel-admin左侧菜单栏怎么默认展开打开
    install.sh: 115: install.sh: Syntax error: "(" unexpected (expecting "}")
    git生成密钥
    git 忽略提交某个指定的文件(不从版本库中删除)
    larael-admin汉化配置中文
    Nginx PHP-Fcgi中因PHP执行时间导致504无限循环中断
  • 原文地址:https://www.cnblogs.com/gered/p/10948928.html
Copyright © 2020-2023  润新知