I love the xml type in SQL Server 2005. Here's a very simple way I made use of it: I audit all the object/schema changes to the database with a simple database-level trigger.

First, I create a very simple table (inside a schema I name 'Audit'):

CREATE TABLE [Audit].[Objects](



[EventID] [int] IDENTITY(1,1) NOT NULL,

[EventData] [xml] NULL,



PRIMARY KEY CLUSTERED 

(

   [EventID] ASC

) WITH (IGNORE_DUP_KEY = OFF) ON [PRIMARY]

) ON [PRIMARY]

Then, the trigger:

CREATE TRIGGER [Trig_AuditObjects]

ON DATABASE

FOR DDL_DATABASE_LEVEL_EVENTS 

AS

INSERT INTO Audit.Objects(EventData)

SELECT EVENTDATA()

GO

ENABLE TRIGGER [Trig_AuditObjects] ON DATABASE

That's it.. now I get a nice neat little xml entry in my table every time a DDL database level event happens, like so:

REMOVED DUE TO INCOMPATIBLITY WITH THE FORUM

The EVENTDATA() function is provided by SQL Server inside a DDL trigger and provides all the data you see above as an xml document.

I like having this during development - it's like a poor man's source control for my schema changes. But also, it could come in very handy for forensic purposes when diagnosing post-rollout issues or accidental schema changes. Anyway, it's simple and handy for what it does.