Output XML directly to file from SQL server

Question

Output the results (XML) of a stored procedure to a file.

I have a stored procedure in SQL server that creates an XML file. It currently displays the resulting XML and I have to manually save as a file.

I have tried to call the procedure from Powershell, as in this question, this works for small files but not for large (>1gb files) as Powershell tries to store the entire thing as a variable and it quickly runs out of memory.

I'm opening this as a new question as I think there should be a way of doing this within SQL server (or a better way of doing it with Powershell).

Hi please share your stored procedure or output type . You can create your XML line by line and output select line from #TmpXmlFile Order by IdLine ASC and then you can build your file line by line in your powershell code. — Sanpas, May 24 '19 at 14:00
Looks like a good job to do using [SQL Server Integration Services (aka SSIS)](https://learn.microsoft.com/en-us/sql/integration-services/sql-server-integration-services). — TT., May 24 '19 at 14:05

David Browne - Microsoft · Accepted Answer · 2019-05-31T14:23:17.817

4

You shouldn't use a stored procedure here. Just use better PowerShell. You can stream large types to and from SQL Server with SqlClient. So you just need to drop down and use ADO.NET instead of using the invoke-sqlcmd convenience method.

EG:

$conString = "server=localhost;database=tempdb;integrated security=true"

$sql = @"

select top (1000*1000) * 
from sys.messages m
cross join sys.objects o
for xml auto

"@

$fn = "c:\temp\out.xml"

$con = new-object System.Data.SqlClient.SqlConnection
$con.connectionstring = $conString
$con.Open()

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

$rdr = $cmd.ExecuteXmlReader()
$w = new-object System.Xml.XmlTextWriter($fn,[System.Text.Encoding]::UTF8)
$w.WriteNode($rdr,$true)
$w.Close()
$rdr.Close()

write-host "Process Memory: $( [System.GC]::GetTotalMemory($false) )"
write-host "File Size: $( (ls $fn)[0].Length )"

outputs

Process Memory: 34738200
File Size: 468194885

edited May 31 '19 at 14:23

answered May 24 '19 at 14:05

David Browne - Microsoft

80,331
6
39
67

Thanks for that. Sorry for not replying last week, I've been away. This method works for my smaller test case but fails after about a minute when I try to run it for the full size file. I get the error: `Exception calling "ExecuteXmlReader" with "0" argument(s): "Execution Timeout Expired. The timeout period elapsed prior to completion of the operation or the server is not responding." At S:\Access and Participation\APP2021\Code\Development\LUMBRIC\XML\PowerShell Test\CreateXML_Method2.ps1:105 char:1 + $rdr = $cmd.ExecuteXmlReader()` – user2980115 May 29 '19 at 07:49
Note, that I've tried adding Connect Timeout=0 to the ConString line - but the error happens so quickly that I don't think it can be a timeout issue. – user2980115 May 29 '19 at 07:52
Ok, thanks for the suggestion. It doesn't seem to be able to handle the large dataset though. I don't know why as I can run the same query in SQL Server without problem. Anyway, I am going for the messier route of running lots of smaller queries then reassembling them afterward. – user2980115 May 31 '19 at 09:24
See the update for how to prevent the SqlCommand from timing out. – David Browne - Microsoft May 31 '19 at 14:23

score 1 · Answer 2 · answered May 24 '19 at 14:09

Other solution if you can you have to build your XML file in Temporary Table line by line and then output and read the result line by line from Powershell or other code :

SQL Example :

/*
**
**  Stored procedure  
**
*/

/*** Effacement: ********************************************************
IF EXISTS ( SELECT name FROM sysobjects
            WHERE type = 'P' AND name = 'procTEST' )
    DROP PROCEDURE procTEST
*** Effacement: ********************************************************/

CREATE PROCEDURE procTEST
AS

CREATE TABLE #TEMP (vInfo VARCHAR(MAX), nLine int)

INSERT INTO #TEMP
SELECT 'Line 1',1
UNION ALL 
SELECT 'Line 2',2
UNION ALL 
SELECT 'Line 3',3

SELECT vInfo FROM #TEMP ORDER BY nLine ASC

SET NOCOUNT OFF

/*** TESTS ****************************************************************************************************************************************
sp_helptext procTEST

--  DROP PROCEDURE procTEST
EXEC procTEST

*** TESTS ****************************************************************************************************************************************/

Powershell Script :

$readconn = New-Object System.Data.OleDb.OleDbConnection
$writeconn = New-Object System.Data.OleDb.OleDbConnection
[string]$connstr="Provider=SQLOLEDB.1;Integrated Security=SSPI;Persist Security Info=False;Initial Catalog=TEST;Data Source=.\XXXXX;Workstation ID=OMEGA2"
$readconn.connectionstring = $connstr
$readconn.open()
$readcmd = New-Object system.Data.OleDb.OleDbCommand
$readcmd.connection=$readconn
$readcmd.commandtext='EXEC procTEST'
$reader = $readcmd.executereader()
# generate header
$hash=@{}
for ($i=0;$i -lt $reader.FieldCount;$i++){
       $hash+=@{$reader.getname($i)=''}
}
$dbrecords=while($reader.read()) {
     for ($i=0;$i -lt $reader.FieldCount;$i++){
          $hash[$reader.getname($i)] = $reader.GetValue($i)

     }
   New-Object PSObject -Property $hash
}
$reader.close()
$readconn.close()
$dbrecords

Hi sorry, I haven't replied to this yet. To be honest I'm not sure how to implement it. Where do I set the location of the output? — user2980115, May 31 '19 at 09:25
This script shows perfectly how to get control over the table fields and names. With this you can create more precise XML. — Rudie Heijnen, Apr 09 '20 at 06:32

Output XML directly to file from SQL server

2 Answers2