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?”

image

 

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' Star

        , 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) Star --Add this to force a newline after the above attributes

The Star 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' Star

        , 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) Star   --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

Published 05 May 2011 00:14 by simonsabin
Filed under: ,

Comments

05 May 2011 00:57 by SimonS Blog on SQL Server Stuff

# 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

31 May 2011 09:36 by simonsabin

# re: Working with large XML files in Management Studio

Inserting a CR works, but if you ever need to extract XML from the database and then send it using a messaging interchange, those CRs might cause problems.

by email from Bill Conniff

31 May 2011 09:41 by simonsabin

# re: Working with large XML files in Management Studio

My understanding is that CR is treated as whitespace and so will be ignored by parsers. However from my experience I know that parsers don't all behave as they should and for that reason you might find this may be a problem but I'm pretty sure you won't.

The other point to note is that SQL puts in CRs directly without you having to. Its only when you have a sub set of elements of an element with text that you don't get these extra CRs.

If its a problem you could put in a debug switch in the query so you can output the CRs when debugging in Management Studio.

i.e. case when @Debug >0 then char(13) else null end Star

The null will mean you don't get the extra text element in your production execution.