How do you format XML output properly from Powershell using invoke-sqlcmd

Question

Below is my PowerShell script that connects to a remote SQL server and stored the result into a XML file.

$SQLResult = Invoke-Sqlcmd -inputfile $inputfile -ServerInstance $ServerInstance -Database $Database -Username $Username -Password $Password 
$PropertyName = ($SQLResult | Get-Member -MemberType Property | Where {$_.Name -like "XML*"}).Name
$SQLResult.$PropertyName | Out-File -FilePath "C:\Temp\ExportFile.xml" -Force

Ideally it should return sth clean and neat like this (Which is also the case when I open up the result in my SQL Server Management Studio):

<Text>
    <Data>I am happy</Data>
</Text>

However, when I open the file, it gives me:

<Text><Data>I am happy</Data></Text>

I have tried to use Export-Clixml, but the XML returned is surrounded by some meaningless tags called <props> which is not one of my tags.

Can anyone help me out on this, not sure which way to go to save it in its original format.

https://stackoverflow.com/questions/33830917/how-to-properly-export-xml-to-file-using-powershell — Doug Maurer, Aug 17 '20 at 18:49

score 2 · Answer 1 · answered Aug 17 '20 at 18:49

2

Use an XmlWriter to format and indent it nicely when writing back to disk:

# Create settings object, make sure we get the indentation
$writerSettings = [System.Xml.XmlWriterSettings]::new()
$writerSettings.Indent = $true

try{
  # Create the writer
  $writer = [System.Xml.XmlWriter]::Create("C:\Temp\ExportFile.xml", $writerSettings)

  # Convert your XML string to an XmlDocument, 
  # then save the document using the writer
  ([xml]$SQLResult.$PropertyName).Save($writer)
}
finally {
  # discard writer (closes the file handle as well)
  $writer.Dispose()
}

answered Aug 17 '20 at 18:49

Mathias R. Jessen

157,619
12
148
206

I tried this but it returns an error: Cannot convert value "System.Object[]" to type "System.Xml.XmlDocument". Error: "'>' is an unexpected token. The expected token is '='. Line 3, position 3." – David Aug 17 '20 at 19:32
@David Sounds like whatever string value `$SQLResult.$PropertyName` resolved to isn't valid XML :) – Mathias R. Jessen Aug 17 '20 at 20:15
Is there a way to see if anything is wrong with my XML file :( rly have no clue – David Aug 18 '20 at 20:12
@David If you just print/output the value of `$SQLResult.$Property` to the screen, what's on line 3? – Mathias R. Jessen Aug 18 '20 at 20:19
PS C:\Users\David Kao> $SQLResult.$Property PS C:\Users\David Kao> – David Aug 18 '20 at 20:26

How do you format XML output properly from Powershell using invoke-sqlcmd

1 Answers1