SQL Server Implicit String Conversion/Concatenation (XML parsing: line <x>, character <y>, unexpected end of input)

If you are getting this error in your SQL Server T-SQL script:, you may be running into an issue with implicit string conversion in SQL Server:

declare @xml varchar(max), @doc XML, @stringVariable varchar(256)
set @stringVariable = 'a string value'

-- @doc is set by concatenating multiple string literals
-- and string variables, with all the variables having less than
-- or equal to 8000 characters
set @xml = '<root>' +
... + @stringVariable +
...
'</root>'

print len(@xml)

set @doc = @xml

Output

8000
Msg 9400, Level 16, State 1, Line 4
XML parsing: line 64, character 74, unexpected end of input

As you can see in the output, the @xml variable was truncated to 8000 characters, resulting in an invalid XML. This is due to the way SQL Server performs implicit string conversions when concatenating strings. When all the string literals/variables involved in the concatenation are 8000 characters or less, the resulting string will be exactly 8000 characters.

The same issue occurs with NVARCHAR data type. Instead of the 8000-character limit, it’s 4000 characters.

A simple fix is to make sure at least one of the string variables is of type VARCHAR(MAX):

declare @xml varchar(max), @doc XML, @stringVariable varchar(256)
declare @x varchar(max)

set @stringVariable = 'a string value'
set @x = ''

set @xml = @x + '<root>' +
... + @stringVariable +
...
'</root>'

print len(@xml)

set @doc = @xml

 

More Info

5 Replies to “SQL Server Implicit String Conversion/Concatenation (XML parsing: line <x>, character <y>, unexpected end of input)”

  1. I have a problem here:

    When I run this query in SSMS, everything works fine , but as soon as I run this as a job , it starts failing with no obvious error:

    XML parsing: line 1 character 484 unexpected end of input [SQLSTATE 42000] (Error 9400).

    I just hope someone can have a solution for this.

  2. I am facing the same problem. When I run the code in SSMS it works absolutely fine but as soon as I create a job for it, it breaks with error:

    Line: 1 : Error message XML parsing: line 20, character 14, unexpected end of input : Error number 9400

    Can someone please help me with this?
    {code}
    IF OBJECT_ID(‘tempdb..#xml’) IS NOT NULL DROP TABLE #xml
    CREATE TABLE #xml ( MyXML xml )

    Select @url = “MyURL”
    exec sp_OACreate ‘MSXML2.XMLHTTP’, @xmlObject OUT;
    exec sp_OAMethod @xmlObject, ‘open’, NULL, ‘get’, @url, ‘false’
    exec sp_OAMethod @xmlObject, ‘send’

    INSERT into #xml ( MyXML )
    EXEC sp_OAGetProperty @xmlObject, ‘responseXML.xml’–, @Response OUT

    Exec sp_OADestroy @xmlObject
    {code}

Leave a Reply

Your email address will not be published. Required fields are marked *