将MySQL查询结果显示为XML

Question

我需要创建一个连接到MySQL实例的PowerShell脚本，运行查询并将结果解析为XML。我从这里开始：

Param(
  [Parameter(
  Mandatory = $true,
  ParameterSetName = '',
  ValueFromPipeline = $true)]
  [string]$Query
)

$MySQLAdminUserName = 'adminuser'
$MySQLAdminPassword = 'password'
$MySQLDatabase = 'mysql'
$MySQLHost = '192.168.10.126'
$ConnectionString = "server=" + $MySQLHost + ";port=3306;uid=" + $MySQLAdminUserName + ";pwd=" + $MySQLAdminPassword + ";database="+$MySQLDatabase

try {
  [void][System.Reflection.Assembly]::LoadWithPartialName("MySql.Data")
  $Connection = New-Object MySql.Data.MySqlClient.MySqlConnection
  $Connection.ConnectionString = $ConnectionString
  $Connection.Open()

  $Command = New-Object MySql.Data.MySqlClient.MySqlCommand($Query, $Connection)
  $DataAdapter = New-Object MySql.Data.MySqlClient.MySqlDataAdapter($Command)
  $DataSet = New-Object System.Data.DataSet
  $RecordCount = $dataAdapter.Fill($dataSet, "data")
  $DataSet.Tables[0] 
} catch {
  Write-Host "ERROR : Unable to run query : $query `n$Error[0]"
} finally {
  $Connection.Close()
}

脚本返回表，但是我无论如何都不能将结果格式化为XML。你能帮我一下吗？我打算运行的所有查询都将显示2列。

Answer 1

对于某些东西来说，这种简单的、成熟的XML构造可能有些过分了。您可以简单地将表值插入到字符串模板中，如下所示：

$results = $DataSet.Tables[0] | ForEach-Object {
  '<result><channel>{0}</channel><value>{1}</value></result>' -f $_.col1, $_.col2
}

$prtg = "<prtg>{0}</prtg>" -f (-join $results)

将col1和col2替换为实际的字段名称。

Answer 2

试试这样的东西

$DataSet.Tables[0].WriteXML('c:\temp\result.xml')

或者这个

[xml] $xml=$DataSet.Tables[0] | ConvertTo-XML -NoTypeInformation