title: how to bypass the 65535 character limit in a select statement using "FOR XML"
body: <p>I am aware of the 65535 truncation for text datatypes (and Varchar(MAX) as well).  I am also aware of XML outputs that can be set to unlimited.  I have a table that stores XML strings in a varchar(MAX) column called GEOM.</p>
  
  <p>My problem is that the xml snippet held in the geom column can exceed 65535 characters.  I figure since it's XML, and I'm creating an XML output in my code anyways, why not just build the xml and send that out as such, since I can set XML output to "Unlimited".</p>
  
  <pre><code>USE [buyerhero]
  GO
  /****** Object:  StoredProcedure [dbo].[GEOIDKMLCOUNTY]    Script Date: 9/17/2015 11:18:55 AM ******/
  SET ANSI_NULLS ON
  GO
  SET QUOTED_IDENTIFIER ON
  GO
  ALTER PROCEDURE [dbo].[GEOIDKMLCOUNTY] @CountyID nvarchar(30)
  AS
  DECLARE @kml XML
  DECLARE @kmlout NVARCHAR(MAX)
  
  SELECT @kml = CAST('&lt;?xml version="1.0" encoding="utf-16" ?&gt;' + 
  '&lt;kml xmlns="http://www.opengis.net/kml/2.2"&gt;' + 
  '&lt;Document&gt;' + 
  '       &lt;Style id="Licensed"&gt;
              &lt;LineStyle&gt;
                  &lt;color&gt;ff000000&lt;/color&gt;
                  &lt;width&gt;2&lt;/width&gt;
              &lt;/LineStyle&gt;
              &lt;PolyStyle&gt;
                  &lt;color&gt;1e1400FF&lt;/color&gt;
                  &lt;fill&gt;1&lt;/fill&gt;
                  &lt;outline&gt;1&lt;/outline&gt;
              &lt;/PolyStyle&gt;
          &lt;/Style&gt;
          &lt;Style id="NotLicensed"&gt;
              &lt;LineStyle&gt;
                  &lt;color&gt;ff000000&lt;/color&gt;
                  &lt;width&gt;2&lt;/width&gt;
              &lt;/LineStyle&gt;
              &lt;PolyStyle&gt;
                  &lt;color&gt;1e14F0FF&lt;/color&gt;
                  &lt;fill&gt;1&lt;/fill&gt;
                  &lt;outline&gt;1&lt;/outline&gt;
              &lt;/PolyStyle&gt;
          &lt;/Style&gt;
          &lt;Style id="Other"&gt;
              &lt;LineStyle&gt;
                  &lt;color&gt;ff000000&lt;/color&gt;
                  &lt;width&gt;2&lt;/width&gt;
              &lt;/LineStyle&gt;
              &lt;PolyStyle&gt;
                  &lt;color&gt;1e007800&lt;/color&gt;
                  &lt;fill&gt;1&lt;/fill&gt;
                  &lt;outline&gt;1&lt;/outline&gt;
              &lt;/PolyStyle&gt;
          &lt;/Style&gt;' +
  (
              select 
                  '&lt;![CDATA[{"County":"' + r.CountyName + ', ' + r.State + '", "GEOID": "'+t.GEOID+'"}]]&gt;' as Name,
                  case t.IsLicensed
                      when 2 then '#Licensed'
                      when 1 then '#NotLicensed'
                      else '#Other'
                  end as StyleURL, 
                  cast(replace(geom,'"','') as xml) as Geometry
          from Tracts t 
          join census_county_ref c on t.GEOID = c.GEOID
          join FIPSCountyCode r on c.STATEFP = r.StateANSI and c.COUNTYFP = r.CountyANSI
          where r.CountyID = @CountyID
  FOR XML PATH(''), ELEMENTS) + '&lt;/Document&gt;&lt;/kml&gt;' AS XML)
  --
  -- Perform replacement of &amp;lt; and &amp;gt; with &lt; and &gt; respectively
  --
  SET @kmlout = REPLACE(REPLACE(CAST(@kml AS NVARCHAR(MAX)), '&amp;lt;', '&lt;'), '&amp;gt;', '&gt;')
  SET @kmlout = REPLACE(@kmlout, 'utf-16', 'utf-8')
  --
  -- Return kmlout
  --
  SELECT @kmlout
  </code></pre>
  
  <p>in spite of the idea the geom columns is xml and I'm using <code>cast(replace(geom,'"','') as xml)</code>  which is varchar(max), that field still gets truncated.</p>
  
  <p>How do I "untruncate" the field so I can output the XML?</p>
  
  <p>Thanks.       </p>