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.

Answer 2

function FormatXmlFile {
param (
    $filePath
)

$xml = Get-Content $filePath -Encoding Unicode
$xml = [xml]$xml
$Indent = 2
$StringWriter = New-Object System.IO.StringWriter
$XmlWriter = New-Object System.XMl.XmlTextWriter $StringWriter
$xmlWriter.Formatting = "indented"
$xmlWriter.Indentation = $Indent
$xml.WriteContentTo($XmlWriter)
$XmlWriter.Flush()
$StringWriter.Flush()
Set-Content -Path $filePath -Value $StringWriter.ToString()
}

Answer 3

function pxml ($xmlfile) {
  $a = [xml](get-content $xmlfile)
  $a.save("$pwd\$xmlfile")
}

Answer 4

You need to convert the data into XML Try this:

[xml]$Result = Invoke-Sqlcmd -Query $Query -database MyDatabase -ServerInstance MyServer 
$Result | out-file $File -Encoding utf8 

or this

Invoke-Sqlcmd -Query $Query -database MyDatabase -ServerInstance MyServer |ConvertTo-XML |Out-File $File -Encoding utf8 

Answer 5

Was really happy with @TechSpud's answer above, but found that empty XML nodes got introduced line breaks.

This version removes the linebreaks from empty XML nodes (or nodes just containing spaces) using a regex replace:

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)
    $sw2 = $sw.ToString()
    $sw2 -replace '<(\w+)>[\s\r\n]*<\/\1>', "<`$1></`$1>"
}

Answer 6

For an in-house script, an easy and pragmatic way to format XML is to pipe it through XML Starlet:

$myXmlObject.OuterXml | xml.exe format --indent-spaces 2

Or:

xml.exe format --indent-spaces 2 myXmlFile.txt

Answer 7

A slightly nicer way if you are ok to save to console out...

$xml = [xml] (gc c:\temp\powershell.xml)
$xml.Save([Console]::Out)

Credit to Dr. Scripto