While teaching SQL Server 2005 to developers, I'd always point out the irony in the fact that the new EVENTDATA() function, used to return information in a DDL trigger or EVENT NOTIFICATION, returns XML and would be used almost exclusively by DBAs. This seems almost like a nefarious plot to force DBAs, who would be the most XML-phobic, to learn XML and XQuery/XPath in order to deal with these in their daily jobs. In fact, I once wrote a SQL statement using XQuery to coerce a common variant of EVENTDATA() output into tabular format.
After spending the last few months wallowing in query plans, deadlock graphs, and the like, I've actually become convinced that XML and XQuery in SQL Server is *primarily a DBA tool*. I knew that would catch your attention. Besides event and query notifications, blocked process events, query plans, deadlock graphs, and EVENTDATA(), Database Tuning Advisor and new bulkload format files also use XML format. XML query plans can be studied by using XQuery/XPath and can then be modified (slightly) for use in "plan forcing" where the XML plan is used as a "query hint", USE PLAN. And there's probably some uses that I missed. SSIS files and Reporting Services RDLs are XML format. I'm becoming convinced that T-SQL error messages should be in XML format too.
So DBAs: if you haven't yet learned XQuery/XPath and the SQL Server XML methods, its not too late. It's there just to make you more productive (well, maybe "just" is an overstatement, but you get the point).
Just to whet your appitite, here's a little stored procedure that uses dynamic management views, the new CROSS APPLY operator and XQuery to look for physical operations in query plans and correlate it with the SQL query. Could be useful, perhaps sorted by execution count. Cheers.
CREATE PROCEDURE LookForPhysicalOps (@op VARCHAR(30))
AS
SELECT sql.text, qs.EXECUTION_COUNT, qs.*, p.*
FROM sys.dm_exec_query_stats AS qs
CROSS APPLY sys.dm_exec_sql_text(sql_handle) sql
CROSS APPLY sys.dm_exec_query_plan(plan_handle) p
WHERE query_plan.exist('
declare default element namespace "http://schemas.microsoft.com/sqlserver/2004/07/showplan";
/ShowPlanXML/BatchSequence/Batch/Statements//RelOp/@PhysicalOp[. = sql:variable("@op")]
') = 1
GO
EXECUTE LookForPhysicalOps 'Clustered Index Scan'
EXECUTE LookForPhysicalOps 'Hash Match'
EXECUTE LookForPhysicalOps 'Table Scan'