Share via

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.

-
Disclaimer:
This posting is provided “AS IS” with no warranties, and confers no rights.
Use of included script samples are subject to the terms specified at https://www.microsoft.com/info/cpyright.htm.

Comments