Invoke-sqlcmd - to export the output of a sql query to an xml file using powershell

Question

I have a sql query with for xml explicit clause. Result i need to store in an xml file. I am using a powershell command to do so : invoke-sqlcmd -inputFile $inputFilePath | Out-File -filePath $outputFilePath

this gives me an output file similar to

XML_F52E2B61-18A1-11d1B1000805F49916B

'<'person>dsugfiugds'<'/person>...

'<'student>sdgfdg<\student>'<'id>45<\id>'<'cla...

ss>sdfsdfsfd<....

This file has also been truncated , you can see the ... appears Where as i need the xml to be in the correct format i.e an xml file itself.

Answer 1

Instead of Out-File have you tried Export-Clixml?

 invoke-sqlcmd -inputFile $inputFilePath | Export-Clixml -filePath $outputFilePath

Answer 2

This works well for me:

$svrParms = @{
  ServerInstance = "somehost.domain.net";
  Username = "myusername";
  Password = "mypassword";
  Database = "theDatabase";
  MaxCharLength = $([int32]::MaxValue);
}

$inFile = ".\mySqlQueryWithXmlResults.sql"
$outFile = ".\myXmlResults.sql"

(Invoke-Sqlcmd @svrParms -InputFile $inFile).Column1 | Out-File $outFile

Edit: It's important to specify MaxCharLength. We hit a limit of 4000 characters in Invoke-Sqlcmd.