How to get an output from Stored PRocedure with text trucation

Greetings.  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 has a text datatype with large strings which appear XML in nature (ie. <coordinates>12.12345 123.123455, 12.11222 123.32323</coordinates>

My problem is that the xml snippet 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, since I can set XML output to "Unlimited".

Here's how I started:
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('<?xml version="1.0" encoding="utf-16" ?>' + 
'<kml xmlns="http://www.opengis.net/kml/2.2">' + 
'<Document>' + 
'       <Style id="Licensed">
            <LineStyle>
                <color>ff000000</color>
                <width>2</width>
            </LineStyle>
            <PolyStyle>
                <color>1e1400FF</color>
                <fill>1</fill>
                <outline>1</outline>
            </PolyStyle>
        </Style>
        <Style id="NotLicensed">
            <LineStyle>
                <color>ff000000</color>
                <width>2</width>
            </LineStyle>
            <PolyStyle>
                <color>1e14F0FF</color>
                <fill>1</fill>
                <outline>1</outline>
            </PolyStyle>
        </Style>
        <Style id="Other">
            <LineStyle>
                <color>ff000000</color>
                <width>2</width>
            </LineStyle>
            <PolyStyle>
                <color>1e007800</color>
                <fill>1</fill>
                <outline>1</outline>
            </PolyStyle>
        </Style>' +
(
			select 
				'<![CDATA[{"County":"' + r.CountyName + ', ' + r.State + '", "GEOID": "'+t.GEOID+'"}]]>' 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) + '</Document></kml>' AS XML)
--
-- Perform replacement of &lt; and &gt; with < and > respectively
--
SET @kmlout = REPLACE(REPLACE(CAST(@kml AS NVARCHAR(MAX)), '&lt;', '<'), '&gt;', '>')
SET @kmlout = REPLACE(@kmlout, 'utf-16', 'utf-8')
--
-- Return kmlout
--
SELECT @kmlout

Open in new window


in spite of the idea that  cast(replace(geom,'"','') as xml)  which is varchar(max)  that field still gets truncated.
How do I "untruncate" the field so I can output the XML?

Thanks.
LVL 9
Evan CutlerVolunteer Chief Information OfficerAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

ste5anSenior DeveloperCommented:
Aehm, what is your concrete problem? Please post a concise and concrete example as runnable T-SQL script. Include table DDL and sample data INSERT statements.
0
Evan CutlerVolunteer Chief Information OfficerAuthor Commented:
This is a reference table.  Not a fact table.  As such, I don't do insert statements.  I truncate and reload.

It's only one column I am having an issue with: geom varchar(MAX).
geom has a large string.  The string is an XML string.  THe string has a tendancy to be larger than 65535, which prohibits standard select stations.  SMSS does allow for XML to go past 65535, but even when querying geom with cast as XML, the string truncates.  I need to know how to prevent that.  The stored procedure I included earlier is how I"m currently running it.

If you can help, I'd be greatful.  Thanks.
0
David Johnson, CD, MVPOwnerCommented:
varchar(max)  
max is 2^31 -1 byte ( 2 GB)
https://msdn.microsoft.com/en-us/library/ms176089.aspx
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Evan CutlerVolunteer Chief Information OfficerAuthor Commented:
You guys are both right.  I found out the problem was not SQL SERVER but in fact the web programming language we were using.  Unlike other scripts such as PHP or Python, We were using COLDFUSION.  Come to find out, the COLDFUSION engine has a limitation selector as well.  That's what the hold up was.

Thanks for the wisdom.
0
_agx_Commented:
(In case someone else has the same issue .... )

the COLDFUSION engine has a limitation selector as well

Do you mean under the DSN Settings? ie

         Advanced Settings > Enable CLOB (off by default)
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
XML

From novice to tech pro — start learning today.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.