XML Data-type Usage By Other SQL Server 2005 Features
Several of the other features within SQL Server 2005 leverage the XML data-type to pass information around. For example, both Service Broker and triggers transfer data around the server in XML data-type instances. This means that you can easily leverage the power of the XQuery language and other XML data-type methods when using these features.
To illustrate this, we create a simple trigger on our database that fires when we create a new table:
create trigger CreateTableTrigger on database for create_table as
select eventdata()
In this case, our trigger is simply outputting the result of the eventdata() function each time a table is created. If we now create a table called Test in this database, we get the following XML instance returned:
<EVENT_INSTANCE>
<EventType>CREATE_TABLE</EventType>
<PostTime>...</PostTime>
<SPID>51</SPID>
<ServerName>...</ServerName>
<LoginName>...</LoginName>
<UserName>dbo</UserName>
<DatabaseName>master</DatabaseName>
<SchemaName>dbo</SchemaName>
<ObjectName>Test</ObjectName>
<ObjectType>TABLE</ObjectType>
<TSQLCommand>
<SetOptions ANSI_NULLS="ON" ANSI_NULL_DEFAULT="ON" ANSI_PADDING="ON" QUOTED_IDENTIFIER="ON" ENCRYPTED="FALSE" />
<CommandText>create table [Test](c1 int )</CommandText>
</TSQLCommand>
</EVENT_INSTANCE>
I have removed some of the information from the XML for security reasons :) Basically, we see from this that the eventdata() function, called from within a trigger, returns an instance of the XML data-type. This means that we are free to go ahead and apply any of the XML data-type methods to this in order to customize the output from our trigger. If we drop the trigger we created earlier and create a new one with the following definition:
drop trigger CreateTableTrigger on database
go
create trigger CreateTableTrigger on database for create_table as
select eventdata().value('( /EVENT_INSTANCE/ObjectName )[1]', 'nvarchar(max)')
Now, if we create a new table (say Test2), we get the following output:
Test2
Which is exactly what we expect if we run the value() method above over the XML returned from the event.