Working with large XML files in Management Studio
Have you ever tried to generate a large XML file using FOR XML and then found Management Studio die when you try and look at the whole XML document (probably so you can save it).
Well the issue is that normally ALL elements and attributes are generated on ONE line. This means that if you generate a moderately large file > 10k you will find that Management Studio may or may not warn you about the document having a an extremely long line of text
“Document contains one or more extremely long lines of text. These lines will cause the editor to respond slowly when you open the file. Do you still want to open the file?”
However you might not get this warning, you’ll just find that SSMS hangs for a period.
I’ve narrowed the issue down to having a text element and then sub elements. i.e.
select 'sometext' 
, o.name
,o.type_desc
,o.create_date
,o.modify_date
,o.is_ms_shipped
,o.is_published
,o.is_schema_published
, (select c.name
,c.precision
,c.scale
,c.is_ansi_padded
,c.is_column_set
,c.is_computed
,TYPE_NAME (c.user_type_id) TypeName
,C.max_length
from sys.columns c
where c.object_id = o.object_id
for xml path ('column'), type)
from sys.objects o
for xml Path ('object'),type
Note the “sometext” text element.
The trick is to add a carriage return text element to your XML document. This is just whitespace ignored by parsers and so won’t affect any processing but it does split the elements over multiple lines.
You add the following to one of the sub queries
CHAR(13) --Add this to force a newline after the above attributes
The indicates the value should be added as a text element and the char(13) generates the new line
This results in the following statement. You can add these where you would like, especially if you have multiple sub queries generating sub XML fragments.
select 'sometext'
, o.name
,o.type_desc
,o.create_date
,o.modify_date
,o.is_ms_shipped
,o.is_published
,o.is_schema_published
, (select c.name
,c.precision
,c.scale
,c.is_ansi_padded
,c.is_column_set
,c.is_computed
,TYPE_NAME (c.user_type_id) TypeName
,C.max_length
,CHAR(13) --Add this to force a newline after the above attributes
from sys.columns c
where c.object_id = o.object_id
for xml path ('column'), type)
from sys.objects o
for xml Path ('object'),type