Change

Col.value('(/EventType/text())[1]','nvarchar(max)') as 'Event Type'
,Col.value('(/PostTime/text())[1]','datetime') as 'Post Time'

to

Col.value('(EventType/text())[1]','nvarchar(max)') as 'Event Type'
,Col.value('(PostTime/text())[1]','datetime') as 'Post Time'

i.e. drop the leading '/'

Hi All,
I wanted to query the xml returned by the eventdata() function in a ddl
trigger to view it in result set.

I made that code but it returned null, any help please?

create trigger DatabaseEvents
on database
for ddl_database_level_events
as
--select
eventdata().value('(/EVENT_INSTANCE/EventType/text())[1]','nvarchar(max)')
declare @data xml
select @data = eventdata()
select
Col.value('(/EventType/text())[1]','nvarchar(max)') as 'Event Type'
,Col.value('(/PostTime/text())[1]','datetime') as 'Post Time'
from @data.nodes('/EVENT_INSTANCE') as EventsTable(Col)
go


Thank you in advance,
Bishoy