PowerShell and SQL Server export XML I have a stored procedure that ends with FOR XML EXPLICIT and I need to put the xml file in a folder. 

I've set up the following PowerShell, and run the script providing the two parameters. 

The name of the script is xml.ps1

./xml "xml" "c:\test.xml"

param
(
    [string]$outputType,
    [string]$filename
    )

$SqlConnection = New-Object System.Data.SqlClient.SqlConnection
$SqlConnection.ConnectionString = "Server=SLISQL;Database=Development_App;Integrated Security=True"
$SqlCmd = New-Object System.Data.SqlClient.SqlCommand
$SqlCmd.CommandText = "dbo._REX_NewSP"
$SqlCmd.Connection = $SqlConnection
$SqlAdapter = New-Object System.Data.SqlClient.SqlDataAdapter
$SqlAdapter.SelectCommand = $SqlCmd
$DataSet = New-Object System.Data.DataSet
$SqlAdapter.Fill($DataSet)
$SqlConnection.Close()
$DataSet.Tables[0]
if ($outputType  -eq "Text") 
{
$DataSet.Tables[0] | format-table -auto &gt; $filename
}

if ($outputType  -eq "xml") 
{
$DataSet.Tables[0] |Export-Clixml $filename
}


I see the xml output in the PowerShell screen, but no text.xml file is output.
I turned your PS into a function, just changing the calls, ran it through the PS ISE, and it worked just fine for me in testing, outputting the data to the location. I would ensure that you have the ability to write to the root of the C: drive. Typically this can require admin permissions. It's possible you are swallowing the error somewhere.

function ProcToXmlFile {
param
(
    [string]$outputType,
    [string]$filename
    )

$SqlConnection = New-Object System.Data.SqlClient.SqlConnection
$SqlConnection.ConnectionString = "Server=localhost;Database=master;Integrated Security=True"
$SqlCmd = New-Object System.Data.SqlClient.SqlCommand
$SqlCmd.CommandText = "select name from sys.databases for XML AUTO"
$SqlCmd.Connection = $SqlConnection
$SqlAdapter = New-Object System.Data.SqlClient.SqlDataAdapter
$SqlAdapter.SelectCommand = $SqlCmd
$DataSet = New-Object System.Data.DataSet
$SqlAdapter.Fill($DataSet)
$SqlConnection.Close()
$DataSet.Tables[0]
if ($outputType  -eq "Text") 
{
$DataSet.Tables[0] | format-table -auto &gt; $filename
}

if ($outputType  -eq "xml") 
{
$DataSet.Tables[0] |Export-Clixml $filename
}
}

ProcToXmlFile "xml" "c:\temp\test.xml"


Created the file in the correct location with the contents:

&lt;Objs Version="1.1.0.1" xmlns="http://schemas.microsoft.com/powershell/2004/04"&gt;
  &lt;Obj RefId="0"&gt;
    &lt;TN RefId="0"&gt;
      &lt;T&gt;System.Data.DataRow&lt;/T&gt;
      &lt;T&gt;System.Object&lt;/T&gt;
    &lt;/TN&gt;
    &lt;ToString&gt;System.Data.DataRow&lt;/ToString&gt;
    &lt;Props&gt;
      &lt;S N="XML_F52E2B61-18A1-11d1-B105-00805F49916B"&gt;&amp;lt;sys.databases name="master"/&amp;gt;&amp;lt;sys.databases name="tempdb"/&amp;gt;&amp;lt;sys.databases name="model"/&amp;gt;&amp;lt;sys.databases name="msdb"/&amp;gt;&amp;lt;sys.databases name="HealthCheck"/&amp;gt;&amp;lt;sys.databases name="RestoreAttachTEST"/&amp;gt;&lt;/S&gt;
    &lt;/Props&gt;
  &lt;/Obj&gt;
&lt;/Objs&gt;


Here's an updated version (a little hacky, I know) that writes the header to a file, and then just the XML data that is produced. See if this gets you a little closer.

function ProcToXmlFile {
param
(
    [string]$outputType,
    [string]$filename
    )

$SqlConnection = New-Object System.Data.SqlClient.SqlConnection
$SqlConnection.ConnectionString = "Server=localhost\sql16;Database=master;Integrated Security=True"
$SqlCmd = New-Object System.Data.SqlClient.SqlCommand
$SqlCmd.CommandText = "select name from sys.databases for XML AUTO"
$SqlCmd.Connection = $SqlConnection
$SqlAdapter = New-Object System.Data.SqlClient.SqlDataAdapter
$SqlAdapter.SelectCommand = $SqlCmd
$DataSet = New-Object System.Data.DataSet
$SqlAdapter.Fill($DataSet)
$SqlConnection.Close()
$DataSet.Tables[0] | Format-Wide -AutoSize
$Headerstring = '&lt;?xml version="1.0" encoding="UTF-8?&gt;'
$Headerstring | Out-File $filename
if ($outputType  -eq "Text") 
{
$DataSet.Tables[0] | Format-Table -HideTableHeaders | out-file $filename -Width 5000 -Append
}

if ($outputType  -eq "xml") 
{
$DataSet.Tables[0] |Export-Clixml $filename
}
}

ProcToXmlFile "text" "c:\temp\test.xml"