Thursday, December 24th, 2009

SQL Server Error Casting XML to VarChar

By Avi Flax

We recently encountered this strange SQL Server error: Target string size is too small to represent the XML instance. Since I didn’t find any clear explanations with a few quick web searches, I thought I’d document the cause.

It turned out that in a stored procedure, we were casting an XML variable to a VarChar, and the VarChar was declared as too small to contain the XML document: CAST(@NotesDoc AS VARCHAR(8000). The fix is simple: change the fixed size (8000) to MAX, as in: CAST(@NotesDoc AS VARCHAR(MAX).

I hope others might find this helpful.

One Response

  1. 1/19/2010
    Joshua Said:

    Just a note, but your CAST() is missing the final “)” in both examples.

    CAST(@NotesDoc AS VARCHAR(MAX)

    should be

    CAST(@NotesDoc AS VARCHAR(MAX))

    Thanks for the article

Leave a Comment