After speaking yesterday and recanting my story (for about the 100th time) about EventData being XML as a \u201cnefarious plot\u201d to require DBAs to learn XML, I wrote this fairly simple XQuery (actually dead simple XPath) function to change EventData into a rowset. It works based on the fact that you’ve deposited EventData into a table that looks like this:<\/p>\n
CREATE TABLE ddl_log (
\nid int primary key identity,
\ndata XML
\n);<\/p>\n
CREATE TRIGGER mytrig
\nON DATABASE
\nFOR CREATE_TABLE
\nAS
\nINSERT ddl_log VALUES(EventData());<\/p>\n
This makes your table into a rowset. You could also use a variation of it in your event notification handler, DDL trigger itself. Just leave out the cross apply. I thought I’d seen this before, but can never seem to ever have found it. Now that I’ve done this, ….learn XML why don’t ‘ya… There can only be more of it in future. Cheers.<\/p>\n
SELECT id,
\nTab.Col.value(‘.\/EventType[1]’,’nvarchar(50)’) AS ‘EventType’,
\nTab.Col.value(‘.\/PostTime[1]’,’datetime’) AS\u00a0 ‘PostTime’,
\nTab.Col.value(‘.\/SPID[1]’,’nvarchar(50)’) AS\u00a0 ‘SPID’,
\nTab.Col.value(‘.\/ServerName[1]’,’nvarchar(50)’) AS\u00a0 ‘ServerName’,
\nTab.Col.value(‘.\/LoginName[1]’,’nvarchar(50)’) AS ‘LoginName’,
\nTab.Col.value(‘.\/UserName[1]’,’nvarchar(50)’) AS ‘UserName’,
\nTab.Col.value(‘.\/DatabaseName[1]’,’nvarchar(128)’) AS ‘DatabaseName’,
\nTab.Col.value(‘.\/SchemaName[1]’,’nvarchar(128)’) AS ‘SchemaName’,
\nTab.Col.value(‘.\/ObjectName[1]’,’nvarchar(128)’) AS ‘ObjectName’,
\nTab.Col.value(‘.\/ObjectType[1]’,’nvarchar(50)’) AS ‘ObjectType’,
\nTab.Col.value(‘.\/TSQLCommand[1]\/CommandText[1]’,’nvarchar(4000)’) AS ‘CommandText’,
\nTab.Col.value(‘.\/TSQLCommand[1]\/SetOptions[1]\/@ANSI_NULLS’,’nvarchar(3)’) AS ‘ANSI_NULLS_OPTION’,
\nTab.Col.value(‘.\/TSQLCommand[1]\/SetOptions[1]\/@ANSI_NULL_DEFAULT’,’nvarchar(3)’) AS ‘ANSI_NULL_DEFAULT_OPTION’,
\nTab.Col.value(‘.\/TSQLCommand[1]\/SetOptions[1]\/@ANSI_PADDING’,’nvarchar(3)’) AS ‘ANSI_PADDING_OPTION’,
\nTab.Col.value(‘.\/TSQLCommand[1]\/SetOptions[1]\/@QUOTED_IDENTIFIER’,’nvarchar(3)’) AS ‘QUOTED_IDENTIFIER_OPTION’,
\nTab.Col.value(‘.\/TSQLCommand[1]\/SetOptions[1]\/@ENCRYPTED_OPTION’,’nvarchar(4)’) AS ‘ENCRYPTED_OPTION’
\nFROM ddl_log
\nCROSS APPLY
\ndata.nodes(‘\/EVENT_INSTANCE’) AS Tab(Col)
\nGO<\/p>\n
— standalone trigger<\/p>\n
CREATE TRIGGER mytrig
\nON DATABASE
\nFOR CREATE_TABLE
\nAS
\nDECLARE @x XML
\nSET @x = Eventdata()
\nSELECT
\nTab.Col.value(‘.\/EventType[1]’,’nvarchar(50)’) AS ‘EventType’,
\nTab.Col.value(‘.\/PostTime[1]’,’datetime’) AS\u00a0 ‘PostTime’,
\n— rest of columns deleted for brevity
\nFROM @x.nodes(‘\/EVENT_INSTANCE’) AS Tab(Col)
\nGO<\/p>\n","protected":false},"excerpt":{"rendered":"
After speaking yesterday and recanting my story (for about the 100th time) about EventData being XML as a \u201cnefarious plot\u201d to require DBAs to learn XML, I wrote this fairly simple XQuery (actually dead simple XPath) function to change EventData into a rowset. It works based on the fact that you’ve deposited EventData into a […]<\/p>\n","protected":false},"author":7,"featured_media":0,"comment_status":"open","ping_status":"closed","sticky":false,"template":"","format":"standard","meta":{"footnotes":""},"categories":[14,28,37],"tags":[],"class_list":["post-890","post","type-post","status-publish","format-standard","hentry","category-event-notifications","category-sql-server-2005","category-sql-server-xml"],"yoast_head":"\n