Can this SQL cause unclosed quotation mark? I've been tracking a bug in how single quotes/apostrohpes are handled in an application, and I've found a suspect in a database trigger.

That trigger contains code like this: (shortened and anonymized)

insert into logTable ([NewRowData]) 
select 
  (select isnull(convert(nvarchar(max), [Foo]),'NULL') as Foo 
   from inserted i where  i.MyID = u.PrsID for xml raw('NewRowData')) 
from @updated_row u


In production, I get errors when "Foo" contains a single quote, (For example "O'Hara")


  Incorrect syntax near 'Hara'. Unclosed quotation mark after the
  character string ',NULL)'.


However, I cannot replicate this. A manual insert/update that fires the trigger does not give an error.

Is is possible that the nested functions isnull and convert somehow messes up quotation escapes or am I looking in the wrong direction? 

(Cannot find any dynamic SQL genereation anywhere, which naturally was my first suspicion.)

Database is SQL server 2012.

Addendum:
I did a similar test to what Vérace posted below before posting here with the same result i.e no exception. If there IS a bug, it must be dependant on database version, settings or query execution plan.
No - it is NOT your SQL! 

To answer this, I did the following:

CREATE TABLE test
(
  num INTEGER,
  name VARCHAR (50)
)

INSERT INTO test VALUES (1, 'Paoul');
INSERT INTO test VALUES (2, 'Mary');
INSERT INTO test VALUES (3, 'O''Hara');
INSERT INTO test VALUES (4, NULL);


then,

SELECT * FROM test;


gives:

num     name
___     _____
1       Paoul
2       Mary
3       O'Hara
4       (null)


And then ran:

SELECT ISNULL(CONVERT(NVARCHAR(MAX), name), 'NULL') AS Foo FROM test;


And got the result:

Foo
____
Paoul
Mary
O'Hara
NULL        &lt;----- String literal, not the value NULL!


Which is what you'd expect! Quite why one would want to insert the string literal NULL is beyond me - Hmm... Developers: "three-value logic, my head is melted - I'll just stick in the string and we can do an OR!". Or, was this system in Oracle at one point? Empty string and NULL string being the same thing?

So, whatever your problem is, it is not your SQL as shown above causing this problem! Check the SQLFiddle here (note: using MS SQL Server 2014!).

As for your point about it being a version bug - I find it difficult to believe that such a basic functionality would be buggy in a 2012 Server version. Such a bug would have broken loads of legacy code! 

My hunch is that the problem lies somewhere with the XML part of the code and the bits we can't see. I would set up logging (of all queries) and check out the SQL text that is actually being sent to the server (on my test system).