Question about querying xml returned by eventdata() function in ddl trigger

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?

createtrigger DatabaseEvents
ondatabase
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

The path expressions in the value methods need to be relative path expressions from the node...

Try:

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