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.

stackoverflow.com/questions/33830917/…

Doug Maurer
– Doug Maurer

2020-08-17 18:49:39 +00:00
Commented Aug 17, 2020 at 18:49 — Doug Maurer
– Doug Maurer, Commented Aug 17, 2020 at 18:49

Mathias R. Jessen · Accepted Answer · 2020-08-17 18:49:04Z

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, 2020 at 18:49

Mathias R. Jessen

178k13 gold badges175 silver badges234 bronze badges

Sign up to request clarification or add additional context in comments.

5 Comments

David Over a year ago

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."

Mathias R. Jessen Over a year ago

@David Sounds like whatever string value $SQLResult.$PropertyName resolved to isn't valid XML :)

David Over a year ago

Is there a way to see if anything is wrong with my XML file :( rly have no clue

Mathias R. Jessen Over a year ago

@David If you just print/output the value of $SQLResult.$Property to the screen, what's on line 3?

David Over a year ago

PS C:\Users\David Kao> $SQLResult.$Property PS C:\Users\David Kao>

Collectives™ on Stack Overflow

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

1 Answer 1

5 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

5 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related