problem selecting xml from sql server 2005 (row shows up as null)

Question

I have a ridiculously long xml row, it has > 800000 characters. The 'client' is also the 'server'. The OS is windows 2k8 x64. The database has plenty of memory as does the client.

The problem is that once i hit some magic number of characters - exactly 43679 xml chars - the row returns what APPEARS to be ''. i call them 'xml chars' because any whitespace is evidently stripped out by sql server when it calculates the length - which i do with this sql: len(convert(varchar(max), xsl)) Why the number is 43679 also baffles me...

Anyone out there have this problem? I've actually attached to the same server with windows 2k3 and another windows 2k8 server and no issues on any other machine than this one (the actual server itself).

I've also found the query results -> sql server -> results to grid and set the limit for xml to 'unlimited' but it seems to still not be 'working'... (the ones that work have this set to 2mb fyi)

Thanks for any and all comments - i'll try anything... it's not preventing anything from working - i'm sure it's just this one client but it's driving me nuts! :)

UPDATE: FYI - the problem isn't with the conversion - i dont' actually want to convert it. the only reason i'm using convert in this example is to use len (len doesn't work on xml datatypes) so I can find how 'long' the xml is.

The problem is this...

INSERT INTO someTableForXml(theXml)
VALUES ('<this><is><some><really>long xml</really></some></is></this>')

Now copy and paste that until you have around 100,000 lines of xml and insert it... When I select the rows back using:

SELECT * FROM someTableForXml

I get back 1 row and in the 'grid' I get a nice little hyperlink for the xml that when I click it, shows me the "full xml" in a new tab. However for ME... Once I pass this oddly magic number it stops doing that and simply looks blank.

Answer 1

You have 2 separate issues I think:

1. SSMS behaviour
2. xml comversion

SSMS behaviour

Try bcp to dump into a file and see what you get. This way you bypass SSMS and it's oddities.

Or CONVERT to varchar(max) so SSMS treats it as LOB data (but see next info)

xml conversion The 43679 is probably caused by the conversion to varchar(max). See CAST AND CONVERT and the style parameter of CONVERT for XML. The default style "discards insignificant white space" and you don't have a style above.

I think SSMS is applying the maximum length of non-LOB data (65535) based on the column with correct CONVERT, but is displaying it without whitespace. Hence the discrepancy.

Answer 2

sadly another question which seems will never be answered...

Answer 3

I think its because you dont have the <?xml version="1.0" encoding="UTF-16"?> in the beginning of your XML.