How to select row and column name where field is null?

Question

I wanted to show row and column where field is null.

i already see this example here

but i don't understand xml code.

my code is like this

if exists (select * from Header where  [name] is Null or [zip] is Null or [age] is Null or [city] is Null )    
    begin
        declare @error1 as varchar (50) 
    select @error1=[id] from Header  where  [name] is Null or [zip] is Null or [age] is Null or [city] is Null  

        declare @error2 AS varchar(50) 

    DECLARE ocur CURSOR FOR 
    select [id] from Header where  [name] is Null or [zip] is Null or [age] is Null or [city] is Null 

    OPEN ocur

    FETCH NEXT FROM ocur 
    INTO @error1

    WHILE @@FETCH_STATUS = 0

    BEGIN
        --set @error2 = @error2 + @error1 + ', '
        set @error2 = 'error -' + @error1 

        insert into yan values(@error2,@uploads, GETDATE() )


        FETCH NEXT FROM ocur INTO @error1
    END

        --insert into table_error values(@error2,@uploads, GETDATE() )
    CLOSE ocur
    DEALLOCATE ocur


    end
else
    begin   
            insert code
            end

so i just need row code that show where column is null.

Thanks

Answer 1

there is no need for using slow cursors ...

Insert into table_error
select 'error - ' + Cast(ID as Varchar(20)), @uploads, GetDate() 
from Header 
where  [name] is Null or [zip] is Null or [age] is Null or [city] is Null

Answer 2

Without XML (hardcoded)

declare @Header table (id int,name varchar(30),zip varchar(10),age int, city varchar(30))
insert into @Header Values(1,'Test','123',21,null);
insert into @Header Values(2,NULL,'123',21,null);
insert into @Header Values(3,NULL,NULL,21,null);
insert into @Header Values(4,'Test','123',21,null);
insert into @Header Values(5,'Test',NULL,21,null);
Select ID
, Case when Name is null then 1 else 0 end
+ Case when zip is null then 1 else 0 end
+ Case when age is null then 1 else 0 end
+ Case when City is null then 1 else 0 end 
as cnt

from @Header

with the output

id     cnt
1   1
2   2
3   3
4   1
5   2