SELECT statement - value gets truncated when selecting from XML column

Question

SELECT statement - value gets truncated when selecting from XML column

iBar

SSCertifiable

Points: 5872
More actions
December 12, 2011 at 2:54 pm

#253288

Hi,
SQL 2005/2008.
While retrieving data from xml column of a table and concatenating with a string (cast(xml_col as varchar(max)), the xml column value gets truncated. why?
SSMS settings are fine as options=> query result output is set to max/unlimited.
Also tried the output to file (and sqlcmd) and still getting the exactly same number of characters (almost 40,000)
Any reason?
Thanks.

Viewing 12 posts - 1 through 11 (of 11 total)

You must be logged in to reply to this topic. Login to reply

Tom257 SSChampion Points: 11543 More actions · Answer 1

Could the XML contain any unicode?

Try NVARCHAR(MAX) - either it works or, if you then get limited to around 20,000 at least we've learned something about the problem.

iBar SSCertifiable Points: 5872 More actions · Answer 2

NO. varchar(max) and Nvarchar(max) are giving the same results.

if XML allow 2GB data then why its stopping at some specific number of characters.

This must be some limitation in settings.

Does someone know any thing like this?

Jeff Moden SSC Guru Points: 1004340 More actions · Answer 3

MidBar (12/12/2011)
NO. varchar(max) and Nvarchar(max) are giving the same results.
if XML allow 2GB data then why its stopping at some specific number of characters.
This must be some limitation in settings.
Does someone know any thing like this?

WHERE is it being truncated? On the screen in the "text" mode? Click on Tools, Options, Query Results, SQL Server, Results to Text and see that each column returned has a limit.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

iBar SSCertifiable Points: 5872 More actions · Answer 4

Hi Jeff,

Yes 8192 is the limit.

I want to read very long xmls from a table and concatenate in with a string to make dynamic SQL and later execute that.

Now is there any way to concatenate those large xmls with a string to make it work.

SSMS grid OR output to text file or even SQLCMD is not working.

OR does this mean we cannot read xml this way at all?

thanks.

Jeff Moden SSC Guru Points: 1004340 More actions · Answer 5

MidBar (12/13/2011)
Hi Jeff,
Yes 8192 is the limit.
I want to read very long xmls from a table and concatenate in with a string to make dynamic SQL and later execute that.
Now is there any way to concatenate those large xmls with a string to make it work.
SSMS grid OR output to text file or even SQLCMD is not working.
OR does this mean we cannot read xml this way at all?
thanks.

What you see and what you have are two different things. You can create and/or work with huge strings and verify that they're huge using LEN or DATALENGTH. To see the different parts, you'll likely need to use SUBSTRING to view them. Truncation on the screen is no indication that the actual data in a table or variable has been truncated.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

Tom257 SSChampion Points: 11543 More actions · Answer 6

8192 rings a bell as a default setting somewhere.

Are you first seeing this truncation when calling a web service, or WCF/SOAP/XML, or some other .NET application?

iBar SSCertifiable Points: 5872 More actions · Answer 7

I am trying the following

select 'EXEC sp_executesql @p1='+cast(xml_col as varchar(max)) OR convert(nvarchar(max), xml_col)

from xml_table

varchar, nvarchar are giving the same effect. Also tried cast and convert and of no use.

Getting the same results in SSMS, output file and also tried SQLCMD.

Interesting factor in this truncation is following

row1 xml_col_len=200,000 and xml gets truncated

row2 xml_col_len=150,000 and even then it gets truncated

so each row has different xml and different string length but even then its not generating complete xml for larger xml. however, for smaller xmls (upto 40,000 i have tested), its fine.

Not reaching to any conclusion that which thing is stopping this?

is this cast/convert limitation? can we use any other function?

Thanks.

GSquared SSC Guru Points: 260824 More actions · Answer 8

First, your script doesn't produce anything useful even if it doesn't truncate, because of missing single-quotes around the param value, and because of not having an actual executable script assigned to the sp_executesql command. I'm assuming that's just a summarizing issue, not something that actually matters.

However, why does it matter if the display in SSMS truncates this? The value you are providing as the parameter (the XML value) isn't truncated in a script, just in the SSMS display.

Try this:

SET NOCOUNT ON;

IF OBJECT_ID(N'tempdb..#XMLTable') IS NOT NULL

DROP TABLE #XMLTable;

CREATE TABLE #XMLTable (

X XML);

INSERT INTO #XMLTable

(X)

VALUES ((SELECT Number FROM dbo.Numbers AS Numbers FOR XML AUTO));

SELECT DATALENGTH(X) FROM #XMLTable;

select LEN('EXEC sp_executesql @p1='+cast(X as varchar(max)))

from #XMLTable;

I have a 10k-row Numbers table. It generates an XML value far in excess of the truncation point. If I display the results of the Select statement (with the Len() function), it gets truncated. But if I build an executable statement, it works without truncation.

In other words, this is a display issue, and a text limit issue, not anything that affects using the XML values as a parameter in a prepared script.

- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread

"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon

drew.allen SSC Guru Points: 76985 More actions · Answer 9

As others have said, this is an issue with what the UI (SSMS) is displaying not with the actual data. This is one of the reasons that I prefer using FOR XML PATH('') to construct my dynamic SQL. The display limit is MUCH larger than for text and is usually more than sufficient for me to check intermediate results.

Drew

J. Drew Allen
Business Intelligence Analyst
Philadelphia, PA

iBar SSCertifiable Points: 5872 More actions · Answer 10

Thanks GSquared and drew,

By checking the #temp table using RIGHT(), i can see the actual prepared string is complete, however, SSMS is not showing that entirely.

One more thing to note, DataLength() and Len() are giving different results.

Thanks everyone.

GSquared SSC Guru Points: 260824 More actions · Answer 11

DataLength and Len can give very different results. Trailing spaces, Unicode strings, possibly a few other things, will cause those to return different numbers.

- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread

"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon