declare @FieldName as varchar(255)
set @FieldName = 'MatchProcNumber'
declare @NodeName as varchar(255)
set @NodeName = (Select distinct v.value('local-name(..)', 'varchar(255)') MyName FROM Formats CROSS APPLY Data.nodes('(*//@*[local-name()=sql:variable("@FieldName")])') TempXML(v))
print @NodeName
Select * from (
Select FormatID, Name, v.query('.') Node,
v.value('(./@*[local-name()=sql:variable("@FieldName")])[1]','varchar(255)') FieldValue
FROM Formats CROSS APPLY Data.nodes('(//*[local-name()=sql:variable("@NodeName")])') TempXML(v)
) t1 where FieldValue is null