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?

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

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

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?

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

Your XPATH expression for the value method is incorrect. You can do one of the following:

select
Col.value('(/EVENT_INSTANCE/EventType/text())[1]','nvarchar(max)') as 'Event Type'
,Col.value('(/EVENT_INSTANCE/PostTime/text())[1]','datetime') as 'Post Time'
from @.data.nodes('/EVENT_INSTANCE') as EventsTable(Col)

-- or

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)

See Books Online EVENTDATA topics for more examples. And check out the XQuery documentation also.

sql

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