CODEWITHSUNDEEP
xmlsql-server-2012large-data
Piotr L
Piotr L

Reputation: 1105

Overcome 2GB limit on XML output in SQL Server

There is a daily process that produces an XML file from an SQL Server database.

Internally it is implemented as a series of nested "FOR XML..." queries enclosed in a stored procedure. A Powershell script executes the stored procedure and processes the output.

Recently, the amount of data generated by the stored procedure exceeded 2 GB and the process died because of a hard limit on XML data type size in SQL Server.

My question is, what would be the best approach to bypass that limit? Obviously, using the XML data type in SQL Server is a no-go but I guess people somehow do generate large XML files off SQL Server databases.

Upvotes: 2

Views: 2255

Answers (1)

Yitzhak Khabinsky
Yitzhak Khabinsky

Reputation: 22275

Please adjust it to test in your environment at the command prompt. It is using Windows Authentication.

"c:\Program Files\Microsoft SQL Server\Client SDK\ODBC\170\Tools\Binn\bcp.exe" "EXEC DBNAME.dbo.usp_name;" queryout "e:\Temp\XMLOutput.xml" -T -x -c -C 1252 -a 32768 -S "DBServerName,1433"

Upvotes: 1

Related Questions