-- Test tabledeclare @T table(ID int, Col1 varchar(10), Col2 int, Col3 bit, Col4 varchar(max))insert into @T values (1, '123', 123, 1, 'Some text long text')-- ID to get one rowdeclare @ID intset @ID = 1-- Value to search fordeclare @Val varchar(10) set @Val = '123'select Col.value('local-name(.)', 'varchar(max)') as ColNamefrom (select * from @T where ID = @ID for xml path(''), type) as T(XMLCol) cross apply T.XMLCol.nodes('*') as n(Col) where Col.value('.', 'varchar(10)') = @Val结果
ColName--------Col1Col2
