Powershell unable to convert file contents to XML

Question

Hello everyone. I have a powershell script that opens Showcase report files and reads the SQL property. It does this by taking the file contents, converting to XML, and looking for the TransformedSQL node. It works great, but sometimes the files are from a prior version, and powershell cannot convert those file contents to XML. Those files are filled with illegal XML characters. I tried removing the illegal characters and converting to XML data type, but then it cannot insert child nodes. The files are not corrupt, because I can open them with Report Writer application.

I would like to know how to convert the file contents of older Showcase reports to XML, when it has illegal characters. Perhaps there is another way to extract the TransformedSQL node while keeping the file contents as Object data type? Any help would be greatly appreciated. Here is a sample of the powershell script:

function Repair-XmlString
{
[CmdletBinding()]
param(
[Parameter(Mandatory=$true,Position=0)]
[string]$inXML
)

Match all characters that does NOT belong in an XML document

$rPattern = "[^\x09\x0A\x0D\x20-\xD7FF\xE000-\xFFFD\x10000\x10FFFF]"

Replace said characters with [String]::Empty and return

return [System.Text.RegularExpressions.Regex]::Replace($inXML,$rPattern,"")
}

User-Defined Variables

$SourceFolder = "H:\Test\goodBad"
$OutputFile = "H:\Test\CombinedQueries.txt"

Get all Showcase report files (including ones in subfolders) and cycle through each one

Get-ChildItem -Path $SourceFolder -File -Include ".rpt", ".dbq" -Recurse |
Foreach-Object {

# Read all contents of the file
$content = Get-Content $_.FullName
$strippedContent = Repair-XmlString ([string]$content)
$xmlContent = [xml]$strippedContent

Here is the error message when it tries to convert to XML
Cannot convert value "System.Object[]" to type "System.Xml.XmlDocument". Error: "' ', hexadecimal value 0x01, is an
invalid character. Line 1, position 2."

Answer 1

Hi,

The invalid character $strippedContent[0][1] should be removed.

Best Regards,
Ian Xue

============================================

If the Answer is helpful, please click "Accept Answer" and upvote it.
Note: Please follow the steps in our documentation to enable e-mail notifications if you want to receive the related email notification for this thread.