New Way Change Capture

NewWayChangeCapture.sql

USE Pantheon;
GO

DECLARE @ChangeLogAsXml xml,
		@Id int = 1;

DROP TABLE IF EXISTS #OutputTableNew;
CREATE TABLE #OutputTableNew
     (
         GotAnyChangeID int         NOT NULL,
         OldColumn1     int         NOT NULL,
         NewColumn1     int         NOT NULL,
         OldColumn2     char(1)     NOT NULL,
         NewColumn2     char(1)     NOT NULL,
         OldColumn3     date        NOT NULL,
         NewColumn3     date        NOT NULL,
         OldColumn4     bigint      NULL,
         NewColumn4     bigint      NULL,
         OldColumn5     varchar(50) NOT NULL,
         NewColumn5     varchar(50) NOT NULL,
         OldColumn6     datetime    NULL,
         NewColumn6     datetime    NULL
     );

UPDATE  gac
SET     gac.Column1 = 2,
        gac.Column2 = 'C',
        gac.Column6 = GETDATE()
OUTPUT	inserted.GotAnyChangeID,
		Deleted.Column1, Inserted.Column1, 
		Deleted.Column2, Inserted.Column2, 
		Deleted.Column3, Inserted.Column3, 
		Deleted.Column4, Inserted.Column4, 
		Deleted.Column5, Inserted.Column5, 
		Deleted.Column6, Inserted.Column6
		INTO #OutputTableNew
FROM	dbo.GotAnyChange AS gac
WHERE	gac.GotAnyChangeID = @Id;

SELECT  New.ColumnName AS CN,
        New.NewValue   AS NV,
        Old.OldValue   AS OV
FROM
        (
            SELECT  Unpvt.GotAnyChangeID,
                    Unpvt.ColumnName,
                    Unpvt.NewValue
            FROM
                    (   SELECT  [GotAnyChangeID],
                                CAST(ISNULL([NewColumn1], '') AS nvarchar(20)) AS [Column1],
                                CAST(ISNULL([NewColumn2], '') AS nvarchar(20)) AS [Column2],
                                CAST(ISNULL([NewColumn3], '') AS nvarchar(20)) AS [Column3],
                                CAST(ISNULL([NewColumn4], '') AS nvarchar(20)) AS [Column4],
                                CAST(ISNULL([NewColumn5], '') AS nvarchar(20)) AS [Column5],
                                CAST(ISNULL([NewColumn6], '') AS nvarchar(20)) AS [Column6]
                        FROM    #OutputTableNew
                    ) AS DataSource UNPIVOT(NewValue FOR ColumnName IN([Column1], [Column2], [Column3], [Column4], [Column5], [Column6])) AS Unpvt
        ) AS New
INNER JOIN
        (
            SELECT  Unpvt.GotAnyChangeID,
                    Unpvt.ColumnName,
                    Unpvt.OldValue
            FROM
                    (   SELECT  [GotAnyChangeID],
                                CAST(ISNULL([OldColumn1], '') AS nvarchar(20)) AS [Column1],
                                CAST(ISNULL([OldColumn2], '') AS nvarchar(20)) AS [Column2],
                                CAST(ISNULL([OldColumn3], '') AS nvarchar(20)) AS [Column3],
                                CAST(ISNULL([OldColumn4], '') AS nvarchar(20)) AS [Column4],
                                CAST(ISNULL([OldColumn5], '') AS nvarchar(20)) AS [Column5],
                                CAST(ISNULL([OldColumn6], '') AS nvarchar(20)) AS [Column6]
                        FROM    #OutputTableNew
                    ) AS DataSource UNPIVOT(OldValue FOR ColumnName IN([Column1], [Column2], [Column3], [Column4], [Column5], [Column6])) AS Unpvt
        ) AS Old
    ON  Old.ColumnName      = New.ColumnName
   AND  Old.GotAnyChangeID = New.GotAnyChangeID
WHERE   New.NewValue <> Old.OldValue
FOR XML PATH('Change'), ROOT('Changes');