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 

Solution 1

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 

Solution 2

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.

Solution 3

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()
}

Comments

[TechSpud]

Out-File has a width limit. See my answer.

[Cornflake2068]

I am getting an error with this " Exception calling "LoadXml" with "1" argument(s): "Data at the root level is invalid. Line 1, position 1." At line:6 char:3 + $xmldoc.LoadXml($xmlcontent) + ~~~~~~~~~~~~~~~~~~~~~~~~~~~~ + CategoryInfo : NotSpecified: (:) [], MethodInvocationException + FullyQualifiedErrorId : DotNetMethodException "

[TechSpud]

Sounds like your XML isn't well formed (maybe multiple root nodes?). Use the example without the function and remove the line <code> | Format-XML `</code>

[Cornflake2068]

it returns each line but instead of the XML it says "System.Data.DataRow"

[TechSpud]

Sorry, thought I'd replied. Store the result in a variable, then pick out the XML data from a column in your DataRow. See amended example.

[Cornflake2068]

Thanks for that. This works, sort of. It only pulls out 4000 characters. I have tried adding -MaxLength to it but the Set-Content cmdlet doesn't recognise that command

[TechSpud]

Is that because of the return type of your SQL stored procedure? Maybe it's set to NVARCHAR(4000) or VARCHAR(4000). If you can change the procedure, set it to return a VARCHAR(MAX)

[Ehtesh Choudhury]

Do you know who the author of Format-XML is?

[TechSpud]

No, sorry, but I guess a quick Google search might help? If I need to add the source/author, please let me know. It was a code snippet I’d saved a long time ago