Selecting xml from SQL Server 2005 (string displays as null)
I have a ridiculously long xml string, it has> 800000 characters. The "client" is also a "server". OS is windows 2k8 x64. The database has a lot of memory, as does the client.
The problem is that as soon as I hit some magic number of chars - exactly 43679 xml chars - the string will return that APPEARS will be. '' I call them "xml chars" because any spaces are obviously removed by the sql server when it calculates the length I do with this sql: len (convert (varchar (max), xsl)) Why the number 43679 also puzzles me. ..
Does anyone have such a problem? I am actually tied to the same 2k3 windows server and another 2k8 windows server and no problem on any other machine other than this one (the server itself).
I also found query results -> sql server -> results to grid and set the xml limit to be "unlimited" but it still doesn't seem to "work" ... (the ones that do have set to 2mb fyi)
Thanks for any comments - I'll try anything ... it doesn't interfere with anything working - I'm sure this is just this client, but it makes me go crazy! :)
UPDATE: FYI - the problem is not the conversion - I don't want to convert it. the only reason i use transform in this example is to use len (len doesn't work with xml datatypes) so i can find how "long" xml.
The problem is that...
INSERT INTO someTableForXml(theXml)
VALUES ('<this><is><some><really>long xml</really></some></is></this>')
Now copy and paste this until you have about 100,000 xml lines and paste it ... When I select the lines with:
SELECT * FROM someTableForXml
I go back to 1 line and in the "grid" I get a little hyperlink for the xml which when I click on it shows me the "full xml" in a new tab. However for me ... When I pass this weird magic number it stops doing it and just looks empty.
source to share
You have 2 separate questions I think:
- SSMS behavior
- xml comversion
SSMS behavior
Try bcp to dump the file and see what you get. This way you get around SSMS and its quirks.
Or CONVERT for varchar (max), so SSMS treats it as LOB data (but see next info)
the xml 43679 conversion is probably caused by the conversion to varchar (max). See CAST AND CONVERT and the style parameter for CONVERT for XML. The default style "discards minor 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 correct CONVERT column, but displays it without spaces. Hence the discrepancy.
source to share