The FOR XML clause is not allowed in a INSERT statement

DECLARE @sql nvarchar(max);
DECLARE @TRANSFER_TYPE_CD nvarchar(50) = 'SF1151';
DECLARE @Transfers XML, @results XML;

DECLARE @tmpTable TABLE (data XML);
DECLARE @nodes TABLE (match XML);

INSERT @tmpTable SELECT ('');
SELECT * FROM @tmpTable;

-- wrap select with select()
select @sql = N'
DECLARE @TRANSFER_TYPE_CD nvarchar(50)=''' + @TRANSFER_TYPE_CD + ''';
SELECT(SELECT
[TRANSFER_XML].query(''//Transfer[@Type=sql:variable("@TRANSFER_TYPE_CD")]'')
FROM [dbo].[IMART_TRANSFER]
FOR XML PATH(''''))';

INSERT into @nodes exec(@sql);
SELECT * from @nodes;

SELECT @results = (SELECT * from @nodes);

UPDATE @tmpTable
SET data.modify('insert sql:variable("@results") into (/root/Transfers)[1]');
SELECT * FROM @tmpTable;