SQL Server stored procedure working when run manually, not running from SQL Server Agent
line 1, character 38, unable to switch the encoding


I ran in to this a while back sql server wants the source string type to match the utf code. 

    SELECT CAST('&lt;?xml version = "1.0" encoding = "utf-8"?&gt;&lt;nobody&gt; &lt;/nobody&gt;' AS XML)
    GO
    SELECT CAST('&lt;?xml version = "1.0" encoding = "utf-16"?&gt;&lt;nobody&gt; &lt;/nobody&gt;' AS XML)
    GO
    SELECT CAST(N'&lt;?xml version = "1.0" encoding = "utf-8"?&gt;&lt;nobody&gt; &lt;/nobody&gt;' AS XML)
    GO
    SELECT CAST(N'&lt;?xml version = "1.0" encoding = "utf-16"?&gt;&lt;nobody&gt; &lt;/nobody&gt;' AS XML)
    GO


You should see only the first and forth query have results. This is where the xml declared utf-"bits per character" matches the sql datatype.


  line 9, character 9, unexpected end of input 


I would run this problem from a different direction. 


have the procedure output the received xml
check for permissions issues with the service accounts (difference between sql server and agent services)