Chinh Do

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

First time here? Check out my greatest hits or look around in the archives, and consider subscribing to the latest posts via RSS or email. I am also on Twitter and Google Plus. Thanks for visiting.
20th February 2010

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

posted in Database, Programming | 6 Comments

  • Calendar

  • September 2017
    M T W T F S S
    « Dec    
     123
    45678910
    11121314151617
    18192021222324
    252627282930