How to get rid of special characters after generating sql statements using for xml

Question

I have generated insert statements for audit trail using for xml and it generates the correct insert statements. See below is what gets generated.

This is what Print @SQL outputs:

INSERT INTO [AuditLog]
                (
                [TableName]
                ,[ColumnName]
                ,[Value]
                ,[Action]
                ,[TimeStamp]
                )           
                SELECT 
                    'Book'
                    ,'BookID'
                    ,CONVERT(VARCHAR(MAX), BookID)
                    ,CONVERT(VARCHAR(MAX), 1)
                    ,getdate()
                FROM INSERTED;
                INSERT INTO [AuditLog]
                (
                [TableName]
                ,[ColumnName]
                ,[Value]
                ,[Action]
                ,[TimeStamp]
                )           
                SELECT 
                    'Book'
                    ,'c_Name'
                    ,CONVERT(VARCHAR(MAX), c_Name)
                    ,CONVERT(VARCHAR(MAX), 1)
                    ,getdate()
                FROM INSERTED;
                INSERT INTO [AuditLog]
                (
                [TableName]
                ,[ColumnName]
                ,[Value]
                ,[Action]
                ,[TimeStamp]
                )           
                SELECT 
                    'Book'
                    ,'c_Author'
                    ,CONVERT(VARCHAR(MAX), c_Author)
                    ,CONVERT(VARCHAR(MAX), 1)
                    ,getdate()
                FROM INSERTED;
                INSERT INTO [AuditLog]
                (
                [TableName]
                ,[ColumnName]
                ,[Value]
                ,[Action]
                ,[TimeStamp]
                )           
                SELECT 
                    'Book'
                    ,'c_Price'
                    ,CONVERT(VARCHAR(MAX), c_Price)
                    ,CONVERT(VARCHAR(MAX), 1)
                    ,getdate()
                FROM INSERTED;

However when I try to execute using EXECUTE(@SQL) I get an error message:

Incorrect syntax near '&'.

Answer 1

Unbelievably, I found really simple solution. I used COALESCE function to generate dynamic insert statements and I don't see any special characters as it would otherwise using for xml