PowerShell导出SQL查询至XML文件出现异常空格问题求助

一、使用的PowerShell脚本 #

$SQLResult = Invoke-Sqlcmd -MaxCharLength ([int]::MaxValue) -AbortOnError -EncryptConnection -ConnectionTimeout $TIMEOUT -Database $Database -ServerInstance $SQL_SERVER_FULLNAME_SOURCE -Username $SQL_ACCOUNT_NAME_SOURCE -Password $SQL_ACCOUNT_PASSWORD_SOURCE -Query $QUERY 
$PropertyName = ($SQLResult | Get-Member -MemberType Property | Where {$_.Name -like "XML*"}).Name
$SQLResult.$PropertyName | Out-File -FilePath $OutputFile -Force

二、预期输出的规范XML #

<vendors>
  <vendor>
    <administration>YIT</administration>
    <FISCALCODE>804658055B01</FISCALCODE>
    <accountNumber>10001</accountNumber>
    <Offset_LedgerAccount></Offset_LedgerAccount>
    <name>Comp Europe B.V.</name>
    <shortName>Comp Europe B.V.</shortName>
    <TAXEXEMPTNUMBER>NL801238055B01</TAXEXEMPTNUMBER>
    <DefaultDescription></DefaultDescription>
    <invoiceType>Non-PO CEE-FN-ACQ</invoiceType>
    <IBAN>NL57MHCB0212303590</IBAN>
    <CUSTOMERACCOUNT></CUSTOMERACCOUNT>
    <TAXITEMGROUP></TAXITEMGROUP>
    <IncludeOrderReference>Unknown</IncludeOrderReference>
    <lineText></lineText>
    <lines>Unknown</lines>
  </vendor>
  <vendor>
    <administration>YIT</administration>
    <FISCALCODE>03840123961</FISCALCODE>
    <accountNumber>20001</accountNumber>
    <Offset_LedgerAccount></Offset_LedgerAccount>
    <name>4ABCD - For Marketing s.r.l.</name>
    <shortName>4ABCD-FOR MARKETING</shortName>
    <TAXEXEMPTNUMBER>03840123961</TAXEXEMPTNUMBER>
    <DefaultDescription></DefaultDescription>
    <invoiceType>Purchase invoice</invoiceType>
    <IBAN>IT93M0306912330615256048252</IBAN>
    <CUSTOMERACCOUNT></CUSTOMERACCOUNT>
    <TAXITEMGROUP></TAXITEMGROUP>
    <IncludeOrderReference>Unknown</IncludeOrderReference>
    <lineText></lineText>
    <lines>Unknown</lines>
  </vendor>
</vendors>

三、实际异常输出 #

实际生成的XML中部分节点莫名出现额外空格，且间隔性重复该异常，例如：

<vendor>
        <administration>YIT</administration>
        <FISCALCODE>0381230961</FISCALCODE>
        <accountNumber>20001</accountNumber>
        <Offset_LedgerAccount/>
        <name>4ABCD - For Marketing s.r.l.</name>
        <shortName>4ABCD-FOR MARKETING</shortName>
        <TAXEXEMPTNUMBER>03840123961</TAXEXEMPTNUMBER>
        <DefaultDescription/>
        <invoiceType>Purchase invoice</invoiceType>
        <IBAN>IT93M03069121238252</IBAN>
        <CUST OMERACCOUNT>< CUSTOMERACCOUNT><TAXITEMGROUP>< TAXITEMGROUP><IncludeOrderReference>Unknown< IncludeOrderReference><lineText>< lineText>

注：数据量可达数千条，但在SSMS中执行相同查询能生成干净的XML文件。

四、使用的SQL查询语句 #

SELECT 
v.[DATAAREAID] AS [administration]
,v.[FISCALCODE]
,v.[VENDORACCOUNTNUMBER] AS [accountNumber]
,v.[DEFAULTOFFSETLEDGERACCOUNTDISPLAYVALUE] AS [Offset_LedgerAccount]
,v.[VENDORORGANIZATIONNAME] AS [name]
,v.[VENDORSEARCHNAME] AS [shortName]
,CASE WHEN LEFT(v.[TAXEXEMPTNUMBER],2)='IT' THEN RIGHT(v.[TAXEXEMPTNUMBER],LEN(v.[TAXEXEMPTNUMBER])-2) ELSE v.[TAXEXEMPTNUMBER] END AS [TAXEXEMPTNUMBER]
,ISNULL(v.[NOTES],'') AS [DefaultDescription]
,ISNULL(ipvp.[INVOICETYPENAME],'') AS [invoiceType]
,ISNULL(vba.[IBAN],'') AS [IBAN]
,ISNULL(c.[CUSTOMERACCOUNT],'') AS [CUSTOMERACCOUNT]
,ISNULL(itc.[TAXITEMGROUP],'') AS [TAXITEMGROUP]
,'Unknown' AS [IncludeOrderReference]
,v.[DESTINATIONCODE] AS [lineText]
,'Unknown' AS [lines]
  FROM [dbo].[RetailVendVendorV3Staging] v
  LEFT OUTER JOIN [dbo].[AXTip_ParametersVendTableStaging] ipvp ON v.[VENDORACCOUNTNUMBER]=ipvp.[VENDORACCOUNT] AND v.[DATAAREAID]=ipvp.[DATAAREAID]
  LEFT OUTER JOIN [dbo].[VendVendorBankAccountStaging] vba ON vba.[DATAAREAID]=v.[DATAAREAID] AND vba.[VENDORACCOUNTNUMBER]=v.[VENDORACCOUNTNUMBER] AND  vba.[VENDORBANKACCOUNTID]=v.[BANKACCOUNTID]
  LEFT OUTER JOIN [dbo].[CustCustomerV3Staging] c ON c.[VENDORACCOUNT]=v.[VENDORACCOUNTNUMBER] AND v.[DATAAREAID]=c.[DATAAREAID]
  LEFT OUTER JOIN [dbo].[AXTip_ImportTaxcodeStaging] itc ON itc.[ACCOUNTRELATION]=v.[VENDORACCOUNTNUMBER] AND itc.[DATAAREAID]=v.[DATAAREAID] AND itc.[EXTERNALTAXCODE]='I'
  WHERE v.[DATAAREAID] ='YIT' AND v.[FISCALCODE]!=''
    FOR XML PATH('vendor'), root('vendors')

问题原因分析 #

1. Invoke-Sqlcmd分段处理缺陷：当XML结果过大时，Invoke-Sqlcmd会将XML拆分为多个字符串片段返回，拼接过程中可能引入额外空格或破坏节点结构。
2. 编码不匹配：Out-File默认编码（Windows-1252）与SQL返回的UTF-8 XML编码不匹配，导致字符解析异常。
3. 属性提取不稳定：依赖Name -like "XML*"提取XML列的方式，当SQL返回列名变化时可能出错，且无法保证获取完整XML内容。

解决方案 #

方案1：用sqlcmd.exe直接输出（推荐） #

绕过Invoke-Sqlcmd的中间处理，直接调用sqlcmd.exe将SQL结果输出到文件，避免分段和编码问题：

& sqlcmd -S $SQL_SERVER_FULLNAME_SOURCE -d $Database -U $SQL_ACCOUNT_NAME_SOURCE -P $SQL_ACCOUNT_PASSWORD_SOURCE -Q $QUERY -o $OutputFile -W -y 0

参数说明：

• -W：移除列宽自动填充的空格
• -y 0：设置无字符长度限制，防止XML截断

方案2：优化PowerShell处理逻辑 #

如果必须使用Invoke-Sqlcmd，调整代码确保获取完整XML并指定正确编码：

# 以DataTables形式获取结果，稳定提取完整XML
$SQLResult = Invoke-Sqlcmd -MaxCharLength ([int]::MaxValue) -AbortOnError -EncryptConnection -ConnectionTimeout $TIMEOUT -Database $Database -ServerInstance $SQL_SERVER_FULLNAME_SOURCE -Username $SQL_ACCOUNT_NAME_SOURCE -Password $SQL_ACCOUNT_PASSWORD_SOURCE -Query $QUERY -OutputAs DataTables

# 直接提取第一行第一列的完整XML内容
$xmlContent = $SQLResult.Rows[0][0]

# 用UTF-8无BOM编码输出，避免解析异常
$xmlContent | Out-File -FilePath $OutputFile -Force -Encoding utf8NoBOM

方案3：排查字段隐藏字符（辅助） #

若怀疑数据本身存在问题，可在SQL中清理字段的不可见空格：

-- 示例：清理CUSTOMERACCOUNT字段的非断空格和普通空格
ISNULL(REPLACE(REPLACE(c.[CUSTOMERACCOUNT], CHAR(160), ''), CHAR(32), ''), '') AS [CUSTOMERACCOUNT]

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