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

TechSpud · Accepted Answer · 2016-09-02 08:15:45Z

15

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.

edited Sep 2, 2016 at 8:15

answered Sep 1, 2016 at 12:37

TechSpud

3,5581 gold badge30 silver badges35 bronze badges

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

8 Comments

Cornflake2068 Over a year ago

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 Over a year ago

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 Over a year ago

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

TechSpud Over a year ago

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 Over a year ago

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

|

Amrit Jain · Accepted Answer · 2021-02-02 22:20:04Z

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

answered Feb 2, 2021 at 22:20

Amrit Jain

1461 silver badge5 bronze badges

Comments

js2010 · Accepted Answer · 2022-06-07 01:03:28Z

1

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

edited Jun 7, 2022 at 1:03

answered Dec 22, 2017 at 21:04

js2010

28.4k6 gold badges82 silver badges88 bronze badges

1 Comment

mklement0 Over a year ago

Your function is severely limited in that it only works with files located in the current directory. Also, consider adding an explanation of what your function does and why you named it that way. The [xml] (get-content ...) idiom is convenient, but not fully robust. In a function, you can easily avoid it.

8DH · Accepted Answer · 2023-02-13 16:17:20Z

1

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

answered Feb 13, 2023 at 16:17

8DH

2,11324 silver badges37 bronze badges

Comments

wonea · Accepted Answer · 2018-06-29 08:29:58Z

0

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

edited Jun 29, 2018 at 8:29

wonea

4,99917 gold badges91 silver badges144 bronze badges

answered Sep 1, 2016 at 10:27

autosvet

8801 gold badge9 silver badges17 bronze badges

2 Comments

TechSpud Over a year ago

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

mklement0 Over a year ago

Out-File doesn't render an [xml] instance as XML text. ConvertTo-Xml isn't meant to be used with XML text input.

widlov · Accepted Answer · 2022-03-29 14:40:29Z

0

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

edited Mar 29, 2022 at 14:40

answered Mar 29, 2022 at 14:28

widlov

1812 silver badges6 bronze badges

Comments

dan-gph · Accepted Answer · 2022-10-17 07:20:47Z

0

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

answered Oct 17, 2022 at 7:20

dan-gph

17.1k13 gold badges65 silver badges83 bronze badges

Collectives™ on Stack Overflow

Formatting XML from PowerShell

7 Answers 7

8 Comments

Comments

1 Comment

Comments

2 Comments

Comments

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

7 Answers 7

8 Comments

Comments

1 Comment

Comments

2 Comments

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related