SQL Server 2005 CLR and XML Demos

SQL Server 2005 provides many new features that can substantially enhance developer productivity and make it easy to perform tasks that used to be difficult in previous versions.  The download available below demonstrates several new SQL Server 2005 features including:

  • Using the XML data type to store RSS feeds
  • Querying XML fields using XQuery with FLOWR statements
  • Converting XML data into a resultset using the new CROSS APPLY syntax
  • Using the new TRY/CATCH syntax
  • Creating stored procedures using C# (shows using SqlContext as well as SqlMetaData and SqlDataRecord classes)
  • Creating custom functions using C#
  • Creating custom aggregates to convert deliminated data into a resultset (and vice-versa)
  • Performing dynamic pivots using the new PIVOT keyword with a custom aggregate

The sample database included in the DBScript folder can be attached to SQL Server Developer or higher if you'd like to experiment.  The .zip file includes a VS.NET 2005 database project that demonstrates how CLR functionality can be used in the database.  Once you attach the database run the following statement in Management Studio since the database does access external resources (RSS feeds on remote sites):

ALTER DATABASE SQL2005Demos SET TRUSTWORTHY ON

Sample Code Download

Published Tuesday, October 3, 2006 2:54 PM by dwahlin
Filed under:

Comments

# re: SQL Server 2005 CLR and XML Demos

Tuesday, October 24, 2006 10:48 AM by Peter Lu

I have set "ALTER DATABASE SQL2005Demos SET TRUSTWORTHY ON" but still getting the error message "... not authorized for PERMISSION_SET = EXTERNAL_ACCESS..."