Large XML Files in SSMS

Question

I tried to import a big XML File in SSMS, but I encounter this problem.

'INSXMLII.XML' is too large to open with XML editor. The maximum file size is '10' MB. Please update the registry key 'HKCU\Software\Microsoft\SQL Server Management Studio\18.0_IsoShell_Config\XmlEditor\MaxFileSizeSupportedByLanguageService' to change the maximum size.

I used method to fix: Navigate to:

"C:\Program Files (x86)\Microsoft SQL Server Management Studio 18\Common7\IDE"

Edit the "Microsoft.XmlEditor.pkgdef" file at approximately line number 322:

Change the line:

"MaxFileSizeSupportedByLanguageService"="10"

To:

"MaxFileSizeSupportedByLanguageService"="99999999999"

Restart SSMS.

However, it encountered this problem:

"Access to the path C:\Program Files (x86)\Microsoft SQL Server Management Studio 18\Common7\IDE\CommonExtensions\Platform\Shell\Microsoft.XmlEditor.pkgdef"

Any thoughts? I have totally no ideas~ Many thanks

99999999999 may too too big. Try something like 100 (you forgot to mention the actual size of the file). — Andrew Morton
My thought is that this is not what SSMS was made for. If the XML is less than 2GB you could import it into a XML column and query it using T-SQL instead. — Cedersved
I usually parse files in a programming language like c# or vb.net and then put data into database. — jdweng
how do you "import"? SSMS is not made to operate on large files, use sqlcmd instead. — devio
@jasonroy7dct, please edit your post and elaborate on "...I tried to import a big XML File in SSMS..." and share your T-SQL. You shared your error, but we don't know what you tried to do, and what caused the error. — Yitzhak Khabinsky

Scrappy Coco Scrappy Coco · Accepted Answer · 2020-04-27T13:48:05

As a workaround you case use this C# code to read all content:

            using (var connection = new SqlConnection(_connectionString))
            using (var sqlCommand = new SqlCommand("..YOUR QUERY..", connection))
            {
                connection.Open();

                var cellStringBuilder = new StringBuilder();
                using (var dataReader = await sqlCommand.ExecuteReaderAsync())
                {
                    while (dataReader.Read()) cellStringBuilder.Append(dataReader.GetString(0));
                }

                // Do something with cellStringBuilder.ToString()...
            }

Large XML Files in SSMS

1 Answers