mysterious
New Member
I'm writing a script to insert rows into a SQL Server 2008 database datatable which contains an XML column. The XML to be inserted is in the form of a concatenated string, so the insert statement looks something like this:\[code\]insert into mytable (id, xmldata) values (100, '<DATA>...<VALUE>' + @varcharvariable + '</VALUE>...</DATA>');(1 row(s) affected)\[/code\]The XML data is between 1000 and 9000 characters long depending on the row being inserted. This usually works fine, however occasionally null is inserted into xmldata instead of the XML. SSMS does not show any errors occurring during the insert, it just silently replaces the XML data with null. If I try to go back and manually update this value the same thing occurs:\[code\]update mytable set xmldata = 'http://stackoverflow.com/questions/11234242/<DATA>...<VALUE>' + @varcharvariable + '</VALUE>...</DATA>' where id = 100;(1 row(s) affected)\[/code\]But when viewing that row in the data table xmldata still comes back as null. To further add to my confusion, this issue does not occur on some databases but does on others for the same rows when using the exact same query.At some point I read this may be related to a memory issue (I can't find the page anymore so I don't remember the specifics). The webpage mentioned prepending CAST('' as VARCHAR(MAX)) to the XML would ensure enough memory was allocated for the query to run as intended. This actually worked for a while, but today I ran into the issue again and adding this was not enough. This would be an example of the updated query:\[code\]update mytable set xmldata = http://stackoverflow.com/questions/11234242/CAST('' as VARCHAR(MAX)) + '<DATA>...<VALUE>' + @varcharvariable + '</VALUE>...</DATA>' where id = 100;\[/code\]Does anyone know what causes this update to silently fail or how I can fix this issue?