The Last Pickle

When using XQuery inside SQL Server the value of SQL variables and columns can be referenced using the sql:column and sql:variable functions respectively. When a SQL value is NULL you may want to mark the XML element as xsi:nil, like the following.

<event_data xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xsi:nil="true" />

The semantic equivalent of SQL NULL in XQuery is an empty sequence. To convert a SQL value to a sequence the XQuery cast operator can be used. This will first atomize the value, then according to the XQuery spec if the result is an empty sequence the cast operation will succeed if the target type has the optional occurrence indicator (’?’ meaning zero or one).

Meaning that the cast must fail and throw an error unless the occurrence indicator is ? present when casting an empty sequence. However the SQL implementation is a little different; from the documentation on SequenceType Expressions…

“In SQL Server 2005, the instance of and cast as XQuery expressions on SequenceTypes are partially supported… In SQL Server, the question mark (?) is required after the AtomicType.”

I imagine SQL added this requirement to their implementation to avoid runtime errors and possibly to continue to the idea that emptyness / NULLness is not a bad thing.

So to detect NULL in a SQL value and optionally create an element with xsi:nil is used the following.

declare @data varchar(128)

declare @x xml
set @x = '';

set @x = 
    @x.query('
        declare namespace xsi="http://www.w3.org/2001/XMLSchema-instance";
        
            
                {   
                    if ( empty(sql:variable("@data") cast as xs:string? )) then                 
                        attribute xsi:nil {"true"}  
                    else () 
                }
                {
                    sql:variable("@data") cast as xs:string?
                }
        
    ');     
select  @x

If the else clause of the if is used to construct an xsi:string node the following error is raised.

XQuery [query()]: Heterogeneous sequences are not allowed: found 'xs:string ?' and
'attribute(xsi{http://www.w3.org/2001/XMLSchema-instance}:nil,xdt:untypedAtomic)'

The Atomic Constructor Functions can also be used to convert values, such as xs:string(@data) however I prefer to use cast when casting variables.