PowerShell and SQL Server export XML

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 > $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:

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

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 = '<?xml version="1.0" encoding="UTF-8?>'
$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"

I realize this is ancient, but I was looking for same thing. Since the posted "answer" did not address the question, I decided to post something similar to what I ended up using.

/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
$SqlTable = New-Object System.Data.DataTable
$SqlTable.Load($SqlCmd.ExecuteReader())
$SqlConnection.Close()
if (0 -lt $SqlTable.Rows.Count)
{
 $Headerstring = '<?xml version="1.0" encoding="UTF-8?>'
 $Headerstring | Out-File $filename -Encoding UTF8
 foreach ($XML in $SqlTable.Rows)
 {
  Out-File -FilePath $filename -Append -Width 1000000000 -NoNewline -InputObject $XML.{XML_F52E2B61-18A1-11d1-B105-00805F49916B}
 }
}