Issue with generating XML file from SQL Server using BCP

Question

I am trying to generate XML file from the view using BCP. View has around 21M records. When I try to generates XML, BCP generate empty file. I checked this process with around 4M records and it works fine.

Please refer below code:

DECLARE @bcpCommand VARCHAR(8000), @FileName VARCHAR(100) = 'Customer_20190830.xml';
SET @bcpCommand
    = 'bcp "SELECT CAST(''<?xml version=""1.0"" ?>'' AS VARCHAR(100)) + CAST((SELECT * FROM dbo.vw_CustomersFOR XML PATH (''Customer''), TYPE, ROOT(''Customers'')) AS VARCHAR(MAX));" queryout E:\Share\Customer\'
      + @FileName + ' -S -d Test-T -c';
PRINT @bcpCommand;
EXEC master..xp_cmdshell @bcpCommand;

Is there any upper limit to writing XML object from SQL Server? Is the any work around? I don't want to generate multiple files.

Answer 1

That loads the whole table into a nvarchar(max) and sends that to the client. It's not an infinitely-scalable approach.

Here's how to stream arbitrary amounts of XML to a file using PowerShell:

# for xml query, not using the TYPE directive, for streaming large results
$sql = "select * from sys.objects for xml path, root('root')"
$fn = "c:\temp\output.xml"
$constr = "Server=localhost;Database=tempdb;integrated security=true"

$con = new-object System.Data.SqlClient.SqlConnection
$con.ConnectionString = $constr 
$con.Open()

$cmd = $con.CreateCommand()
$cmd.CommandTimeout = 0
$cmd.CommandText = $sql

$xr = $cmd.ExecuteXmlReader()
$fs = [System.IO.File]::OpenWrite($fn)
$xw = [System.Xml.XmlWriter]::Create($fs)

$xw.WriteNode($xr,$true)

$xw.Close()
$fs.Close()
$xr.Close()
$con.Close()