（4.31）sql server中的xml数据操作
关键词：xml数据转为行列方式显示
纯XML在sql server中的操作参考：https://www.cnblogs.com/gered/p/9109916.html
常规案例：
XML与表格的互相转换
【1】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>
【2】xml转换成表格形式查看
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
结果：
快捷XML转换成表格（openxml）
参考官网：openxml
将 value() 和 nodes() 方法用于 OPENXML
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
