Yes, I have this covered in another section of the XML file where the match keys are defined.

Okay can you guys see both pictures now? I wonder if this is why no has replaied until now!

Anyway, I've started buidling strings to generatet the select part of the final insert and then I will also generate strings for the CROSS apply part where I generate the required values to populate the staging table.

Something like this:

DECLARE @mkNameKeySELECTString NVARCHAR(100)

DECLARE @mkName1SELECTString NVARCHAR(100)

DECLARE @mkName2SELECTString NVARCHAR(100)

DECLARE @mkName3SELECTString NVARCHAR(100)

-- If we already have First and Last names then there is no need to split the names

IF EXISTS (SELECT 1 FROM dbo.FieldMappings WHERE StagingColumn = 'FirstNames')

AND EXISTS (SELECT 1 FROM dbo.FieldMappings WHERE StagingColumn = 'LastName')

BEGIN

SET @mkNameKeySELECTString = 'dbo.NYSIISPhoneticEncoder(dbo.GetLastWord(' + dbo.GetSourceColumnName('LastName') + ')) + LEFT(' + dbo.GetSourceColumnName('FirstNames') + ', 1)'

SELECT @mkNameKeySELECTString as mkName1

SET @mkName1SELECTString = 'dbo.NYSIISPhoneticEncoder(dbo.GetFirstWord(' + dbo.GetSourceColumnName('Firstnames') + '))'

SELECT @mkName1SELECTString as mkName2

SET @mkName2SELECTString = 'dbo.NYSIISPhoneticEncoder(dbo.GetSecondWord(' + dbo.GetSourceColumnName('FirstNames') + '))'

SELECT @mkName2SELECTString as mkName3

END