The Last Pickle

It’s possible to define an XML column as a computed column. And in most cases I think it would make sense to persist the column to save doing all that work again. As Bob Beauchemin points out there’s a trick to doing it.

The first thing you need is a user defined scalar function to that returns an XML type. As with all persisted computed columns the function must be deterministic and as Bob points out that’s fine because all XQuery functions are deterministic. However the UDF must also be marked with SCHEMABINDING to ensure it’s determinism.

SCHEMABINDING guarantees that the dependancies of the function cannot be changed without first altering the function which has two effects. First the function will continue to execute without an exception (e.g. bad column name), which could be considered a type of determinism. Second column types and definitions cannot change; changes could lead to truncation, overflow or a dependancy on a column changed to a non deterministic computed definition.

So first the function, you’ll need to pass all the column values into the function.

create function dbo.udf_event_data
(
    @start_time as smalldatetime, 
    @end_time as smalldatetime  
)
returns xml 
with schemabinding
as
begin

-- times must be cast to formatted strings for XQuery
declare @start_time_char varchar(128)
set @start_time_char = convert(varchar(128), @start_time, 126) + 'Z'    

declare @end_time_char varchar(128)
set @end_time_char = convert(varchar(128), @end_time, 126) + 'Z'    

declare @x xml
set @x = '';

set @x = 
    @x.query('
        declare namespace xsi="http://www.w3.org/2001/XMLSchema-instance";

        
                    
                {   
                    if ( empty(sql:variable("@start_time_char") cast as xs:dateTime? )) then                    
                        attribute xsi:nil {"true"}  
                    else () 
                }
                {
                    sql:variable("@start_time_char") cast as xs:dateTime?
                }
            
                  
                {   
                    if ( empty(sql:variable("@end_time_char") cast as xs:dateTime? )) then                  
                        attribute xsi:nil {"true"}  
                    else () 
                }
                {
                    sql:variable("@end_time_char") cast as xs:dateTime?
                }
            
        
    ');     
return @x
end     

The next step is to create a computed column and mark the XML column as PERSISTED. All the columns values needed by the function must be passed in the definition which should look something like.

...
[event_data]  AS ([dbo].[udf_event_data]([start_time],[end_time])) PERSISTED,

The UDF above uses the mechanism for handling SQL NULL discussed previously.