您可以使用 DYNAMIC sql
TagsTable应该具有所有可能的标签
create table TagsTable
( tagName varchar(256)
)
insert into TagsTable values ('CLIENT')
insert into TagsTable values ('FEE')
insert into TagsTable values ('ADDRESS')
declare @query nvarchar(max)
SELECT @query = STUFF((select ',' + 'coalesce(Cast(ClientData as xml).value(''(/XML/'
+ tagName + ')[1]'', ''varchar(max)''), '''') as ' + tagName +' '
FROM TagsTable
FOR XML PATH ('') ), 1,1,'')
SET @query = 'SELECT ' + @query + 'FROM dbo.Table2 WITH(NOLOCK)'
select @query
exec sp_executesql @query