如何让PowerShell输出匹配SSMS FOR XML AUTO,ELEMENTS格式的XML？

问题 #

编写了PowerShell脚本用于执行3个SQL查询并输出XML文件，在SSMS中运行带FOR XML AUTO, ELEMENTS的查询能得到符合需求的XML格式，但用PowerShell脚本执行后出现以下问题：

• 输出的XML开头带有PowerShell特定节点<Objs xmlns="http://schemas.microsoft.com/powershell/2004/04" Version="1.1.0.1">
• 列名以<S N=>、<I32 N=>等形式呈现，而非预期的标签格式
• 给查询文件添加FOR XML AUTO, ELEMENTS后，输出数据全部连在一起无换行

当前脚本如下：

# Define SQL Server connection parameters
 $server = "xxx"
 $database = "xxx"
 $username = "xxx"
 $password = "xxx"

 # Define the directory containing SQL query files
 $queryDirectory = "C:\Users\xxx\Desktop\Interface"

 # Define the directory to save the result XML files
 $resultDirectory = "C:\Users\xxx\Desktop\Results"

 # Generate timestamp for the file name
 $timestamp = Get-Date -Format "yyyyMMdd_HHmmss"

 # Define function to execute SQL query and export results to XML
 function Execute-SqlQueryAndExportToXml {
     param (
         [string] $queryFileName,
         [string] $resultFileName
     )

     $queryFile = Join-Path -Path $queryDirectory -ChildPath $queryFileName
     $outputFile = Join-Path -Path $resultDirectory -ChildPath "$resultFileName.xml"

     Invoke-Sqlcmd -ServerInstance $server -Database $database -Username $username -Password $password -trustservercertificate  -InputFile $queryFile  |
     Export-clixml -Path $outputFile
 }

 # Execute each SQL query and export results to XML
 Execute-SqlQueryAndExportToXml -queryFileName "booking.sql" -resultFileName "booking_$timestamp"
 Execute-SqlQueryAndExportToXml -queryFileName "release.sql" -resultFileName "release_$timestamp"
 Execute-SqlQueryAndExportToXml -queryFileName "incident.sql" -resultFileName "incident_$timestamp"

期望的XML格式示例：

<jinames>
  <book_id>xxxx</book_id>
  <name_id>xxxx</name_id>
  <jimain>
    <location>xxxx</location>
    <Type>xxx</Type>
    <incdate>xxx-xxx-xxx:xx:xxx</incdate>
    <inctime>xxxx</inctime>
    <jimainid>xxx</jimainid>
    <locationcd>xxx</locationcd>
    <jidisc>
      <dispositn>xxx</dispositn>
      <jiviol>
        <violcode>xxxx</violcode>
        <violdesc>xxxxxxxxxx</violdesc>
      </jiviol>
    </jidisc>
  </jimain>
</jinames>

解决方案 #

问题核心是Export-Clixml——这个cmdlet用于序列化PowerShell对象，输出的是PowerShell专用XML格式，并非SQL返回的原生XML。要获取和SSMS一致的格式，需直接获取SQL返回的XML结果并做格式化处理。

修改步骤 #

1. 调整SQL查询文件：确保查询末尾添加FOR XML AUTO, ELEMENTS, ROOT('jinames')（ROOT节点名称可根据需求修改为示例中的jinames），让SQL Server直接返回完整的结构化XML。
2. 替换输出逻辑：去掉Export-Clixml，改用直接获取XML字符串并通过XML对象格式化后写入文件的方式，保证正确的换行和缩进。

修改后的完整脚本 #

# Define SQL Server connection parameters
$server = "xxx"
$database = "xxx"
$username = "xxx"
$password = "xxx"

# Define the directory containing SQL query files
$queryDirectory = "C:\Users\xxx\Desktop\Interface"

# Define the directory to save the result XML files
$resultDirectory = "C:\Users\xxx\Desktop\Results"

# Generate timestamp for the file name
$timestamp = Get-Date -Format "yyyyMMdd_HHmmss"

# Define function to execute SQL query and export native XML
function Execute-SqlQueryAndExportToXml {
    param (
        [string] $queryFileName,
        [string] $resultFileName
    )

    $queryFile = Join-Path -Path $queryDirectory -ChildPath $queryFileName
    $outputFile = Join-Path -Path $resultDirectory -ChildPath "$resultFileName.xml"

    # Execute query and get XML result as a single string
    $xmlResult = Invoke-Sqlcmd -ServerInstance $server -Database $database -Username $username -Password $password -TrustServerCertificate -InputFile $queryFile -SingleValue

    # Format XML with indentation and line breaks
    $xml = [xml]$xmlResult
    $xmlSettings = New-Object System.Xml.XmlWriterSettings
    $xmlSettings.Indent = $true
    $xmlSettings.IndentChars = "  "
    $xmlSettings.OmitXmlDeclaration = $false # 若不需要XML声明可改为true

    # Write formatted XML to file
    $xmlWriter = [System.Xml.XmlWriter]::Create($outputFile, $xmlSettings)
    $xml.WriteTo($xmlWriter)
    $xmlWriter.Flush()
    $xmlWriter.Close()
}

# Execute each SQL query and export results to XML
Execute-SqlQueryAndExportToXml -queryFileName "booking.sql" -resultFileName "booking_$timestamp"
Execute-SqlQueryAndExportToXml -queryFileName "release.sql" -resultFileName "release_$timestamp"
Execute-SqlQueryAndExportToXml -queryFileName "incident.sql" -resultFileName "incident_$timestamp"

关键修改说明 #

• -SingleValue参数：让Invoke-Sqlcmd直接返回完整的XML字符串，避免结果被拆分为多个零散对象。
• XML格式化：通过[xml]类型转换和XmlWriterSettings设置缩进，确保输出的XML和SSMS中显示的格式一致，带有换行和层级缩进。
• ROOT节点：SQL查询中添加ROOT参数后，会生成包含根节点的完整XML结构，避免结果缺失层级。

内容的提问来源于stack exchange，提问作者voltagejim