Cornflake2068 Cornflake2068 - 7 days ago 6
SQL Question

Powershell XML encoding

I have a script that executes a stored procedure on a SQL Server which returns XML. I then have a function to to format the XML in powershell so it is readable. When i open the XML in Chrome i get this error:

This page contains the following errors:

error on line 149 at column 27: Encoding error
Below is a rendering of the page up to the first error.


I think I may need to encode it in UTF8 but I am unsure where to do it in my code. Any help to rectify the error or how to do the encoding is appreciated.

Here is the Powershell that I run to get the XML file:

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"

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


Format-xml $resultRow['results'] | Set-Content -Path $File -Force

Answer

Comment "Try appending -Encoding UTF8 to your last line" from Martin Brandi worked