Counting Null Values in a Row

On StackOverflow today someone asked how to get the number of null values in a table’s row; i.e. if there are 10 columns, and 4 of the columns contain data, the answer would be 6.

The requirement was that it would work in sqlite, which I don’t have a generic solution for, but it did prompt me to play around with this in SQL Server.  The result is fairly tidy:

with xmltab(xmlcol) as
    (select cast(
        (select
            *
        from
            myTable
        where
            myIdCol = 1
        for xml path('MYTABLE'), elements xsinil
    ) as xml) as xmlcol
)
select cast(cast(xmlcol.query('count(//*[@xsi:nil="true"])') as varchar) as int) from xmltab