Quick suggestion, add DISTINCT to the select otherwise the two data/entity nodes will always produce duplicate output

😎

DECLARE @XMLXML = '<xml as above>'

DECLARE @SQL nvarchar(max) =

'SELECT DISTINCT '

+(

SELECT ',''' + T.FieldValue+ ''' as '+quotename(T.FieldCode)

FROM (

SELECTT.X.value('@code', 'nvarchar(128)') as FieldCode

,T.X.value('@value', 'nvarchar(128)') as FieldValue

FROM@XML.nodes('/data/entity[1]/fields/field') as T(X)

) AS T

FOR XML PATH(''), TYPE

).value('substring(text()[1], 2)', 'nvarchar(max)')+' '+

' FROM @XML.nodes(''/data/entity'') as T(X)';

SELECT @SQL

exec sp_executesql @SQL, N'@XML xml', @XML;