Formatting XML from PowerShell

Question

I have a PowerShell script that runs a stored procedure which returns XML. I then export the XML into a file but when I open the file, each line has 3 dots at the end and the line isn't complete. This is with using out-file.

When I use Export-Clixml the XML that is returned from the query is dumped in a tag called <props> which is not one of my tags.

I am unsure where to go from here to save my XML in it's original format.

The PowerShell Script that I am using is similar to this:

$Date = Get-Date -format "yyyyMMdd_HHmm" 
$File = "C:\Temp\MyFile"+$Date+".xml"

$Query = "exec dbo.usp_MyProc"

Invoke-Sqlcmd -Query $Query -database MyDatabase -ServerInstance MyServer | out-file $File -Encoding utf8 

Answer 1

Assuming the XML returned from your SQL command is well formed XML, you could also push the XML string through .net's formatting (essentially a pretty printing of the XML).

function Format-XML {
  [CmdletBinding()]
  Param ([Parameter(ValueFromPipeline=$true,Mandatory=$true)][string]$xmlcontent)
  $xmldoc = New-Object -TypeName System.Xml.XmlDocument
  $xmldoc.LoadXml($xmlcontent)
  $sw = New-Object System.IO.StringWriter
  $writer = New-Object System.Xml.XmlTextwriter($sw)
  $writer.Formatting = [System.XML.Formatting]::Indented
  $xmldoc.WriteContentTo($writer)
  $sw.ToString()
}

$Date = Get-Date -format "yyyyMMdd_HHmm" 
$File = "C:\Temp\MyFile"+$Date+".xml"

$Query = "exec dbo.usp_MyProc"

Invoke-Sqlcmd -Query $Query -database MyDatabase -ServerInstance MyServer `
  | Format-XML `
  | Set-Content -Path $File -Force

Export-CliXml exports PowerShell XML, including type information, that can be loaded from disk to rehydrate a variable - hence the extra information.

Out-File has a default width, set by the host PowerShell environment. See Get-Help Out-File -Full.

An example without the XML formatting, storing the DataRow result and picking out the XML column.

$Date = Get-Date -format "yyyyMMdd_HHmm" 
$File = "C:\Temp\MyFile"+$Date+".xml"

$Query = "exec dbo.usp_MyProc"

$resultRow = Invoke-Sqlcmd -Query $Query -database MyDatabase -ServerInstance MyServer


$resultRow['XMLColumn'] | Set-Content -Path $File -Force

You'll obviously need to rename XMLColumn with the name of the column from your stored procedure.