title: How can I create an UPDATE statement from a large XML data type?
body: <p>I am working with two databases that are not accessible at the same time. One of the standard methods of dealing with this I've seen on here is to create dynamic sql for loading one from the other.  </p>
  
  <p>I created a stored procedure that would drop update statements from an existing database. My issue is what happens when the XML is too large to be held in a VARCHAR(max). </p>
  
  <p>Here is a relevant snippet from my attempt where field2 is actually of an XML data type:</p>
  
  <pre><code>DECLARE @field1Col VARCHAR(50)
  DECLARE @field2Col VARCHAR(max)
  DECLARE @vsSQL VARCHAR(max)
  DECLARE curUpdates CURSOR FOR
  -- field 1 is varchar(50), not null
  -- field 2 is XML(.), null
  SELECT
         t.field1
        ,REPLACE(CAST(t.[field2] AS VARCHAR(max)), '''', '''''') 
    FROM
         myTable t
   WHERE
         t.criteria = 0
  OPEN curUpdates
  FETCH NEXT FROM curUpdates INTO @field1Col, @field2Col
  WHILE @@FETCH_STATUS = 0 
  BEGIN
      SET @vsSQL = 'UPDATE dbo.myTable SET [field1] = ''' + @field1Col+ ''' WHERE [field2] = ''' + @field2Col + ''''      
      INSERT INTO @tmp ( SQLText ) VALUES ( @vsSQL )
      FETCH NEXT FROM curUpdates INTO @field1Col, @field2Col
  END
  CLOSE curUpdates
  DEALLOCATE curUpdates
  
  SET NOCOUNT OFF;
  SELECT * FROM @tmp 
  </code></pre>
  
  <p>The issue I have is that even using VARCHAR(max), the XML will sometimes overrun the size. The end product just stops when it reaches the so many characters (the max size of a VARCHAR?).</p>
  
  <p>Is there another approach for working with large XML (splitting into chunks, avoid casting, etc.) where I can build a string of update statements from it?</p>