String Concatination - concat many xml column as varchar(max) do not concatinate all columns in resultant column

Question

String Concatination - concat many xml column as varchar(max) do not concatinate all columns in resultant column

iBar

SSCertifiable

Points: 5872
More actions
July 19, 2011 at 2:15 pm

#253948

Hi,
I have a large table with 10 xml columns with large xml data.
Now i want to join those columns to make one main xml which is combination of all xml by using "+" with all columns as cast(varchar(max)).
BUT it do not concat values of all columns and show values from only first 4 columns.
why?
if i copy-paste individual columns to make one xml then merged xml looks fine.
Note: SSMS editor setting for text and xml is already max.
Thanks.

Viewing 5 posts - 1 through 4 (of 4 total)

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

Lowell SSC Guru Points: 323518 More actions · Answer 1

check the datalength() of your results instead of viewing the column;

I believe for performance sake, no matter what you set the results setting to in SSMS , it displays(at max) the first 8000 chars, even though the data is larger than that.

you might need to send the results to something other than SSMS to confirm the data is correct.

Lowell

--help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

iBar SSCertifiable Points: 5872 More actions · Answer 2

Well size of combined one xml is not even 1MB.

Now i have added a new varchar(max) column in the table and did a simple update

update table set new_col = col1+col2+col3+col4+....

now even then its not showing everything in resultant column.

Why?

I have now output the top 1 resultant new column into text file and even then its showing 8192 characters in that file.

What is this setting?

Lowell SSC Guru Points: 323518 More actions · Answer 3

test this:

SELECT DATALENGTH(new_col) AS TOTALLENGTH,

DATALENGTH(col1) + DATALENGTH(col2) +DATALENGTH(col3) + DATALENGTH(col4) As ShouldBeEqual

FROM table

I think you'll find that the data in the table is the right size and is correct; it's SSMS not displaying the data to you, and that makes you think the data's invalid when it really is OK.

Lowell

--help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

iBar SSCertifiable Points: 5872 More actions · Answer 4

Yes you are right. t

The combined datalength(new_resultant_col) is more than 100,000.

But even then i can't see that even in note pad text file.

Why? and how to see such things ?

I wonder why not SSMS?

Thanks,.