处理Invoke-RestMethod返回的嵌套JSON并导入SQL遇类型匹配问题

通过WhatConverts API拉取数据导入SQL表，返回的JSON外层是对象，但嵌套元素的名称和类型完全可变。计划将基础类型数据直接导入SQL，嵌套数据转为XML后存入SQL，在把Invoke-RestMethod返回的PSCustomObject数组转DataTable时，处理嵌套数据出现类型不匹配错误。

相关代码 #

#NOTE $importedData is an array of PSCustomObject returned from Invoke-RestMethod
$manipulatedData = New-Object System.Data.DataTable
$importedData | ForEach-Object {
    $newRow = $manipulatedData.NewRow()
    foreach ($prop in $_.PSObject.Properties) {
        # nested object
        if ($prop.Value -is [System.Management.Automation.PSCustomObject]) { 
            $tempPropname = $prop.Name
            if (-not ($manipulatedData.Columns.Contains($tempPropname))) {
                $newCol = New-Object System.Data.DataColumn($tempPropname)
                $manipulatedData.Columns.Add($newCol, "System.String" ) | Out-Null
            }
            #$prop | Select-Object Name,Value | ConvertTo-Xml -As String
            $newRow.$tempPropname = $prop | Select-Object Name,Value | ConvertTo-Xml -As String
        }
        # primitive value
        else {
            $tempPropname = $prop.Name 
            if (-not ($manipulatedData.Columns.Contains(($tempPropname)))) {
                $newCol = New-Object System.Data.DataColumn($tempPropname)
                $manipulatedData.Columns.Add($newCol, $prop.TypeNameOfValue ) | Out-Null
            }
            if ($prop.Value -ne '{}') {
                $newRow.$tempPropname = $prop.Value
            }
        }
    }
    $manipulatedData.Rows.Add($newRow)
}

报错信息 #

SetValueInvocationException:
Line |
90 | $newRow.$tempPropname = $prop | Select-Object Name,Value …
| ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
| Exception setting "additional_fields": "Type of value has a mismatch with column typeCouldn't store <

return (<Objects>   <Object Type="System.Management.Automation.PSCustomObject">     <Property Name="Name"
    | Type="System.String">additional_fields</Property>     <Property Name="Value" Type="System.Management.Automation.PSCustomObject">       <Property Type="System.String">@{Junk?=No; New Client?=Not Set; Service-Based?=Not Set}</Property>       <Property Name="Junk?"
    | Type="System.Management.Automation.PSNoteProperty">No</Property>       <Property Name="New Client?" Type="System.Management.Automation.PSNoteProperty">Not Set</Property>       <Property Name="Service-Based?" Type="System.Management.Automation.PSNoteProperty">Not
    | Set</Property>     </Property>   </Object> </Objects>)

> in additional_fields Column. Expected type is Object[].

问题根源 #

报错提示期望类型是Object[]，实际传入XML字符串。核心原因是第一次处理嵌套属性时，DataTable自动把列类型推断成了Object[]，后续手动修改列类型无效——DataTable的列类型一旦创建就无法更改，导致后续赋值字符串时类型不匹配。

修复步骤 #

1. 提前扫描所有数据，预定义列类型：遍历所有PSCustomObject的属性，标记哪些是嵌套对象（要存为字符串），哪些是基础类型，提前创建对应列，避免自动推断错误。
2. 正确转换嵌套对象为XML字符串：直接对嵌套对象的Value转XML，而非对属性本身，减少冗余内容，确保输出纯字符串。

修复后的代码 #

#NOTE $importedData is an array of PSCustomObject returned from Invoke-RestMethod
$manipulatedData = New-Object System.Data.DataTable

# 第一步：提前扫描所有属性，确定每个列的类型
$allProperties = @{}
foreach ($item in $importedData) {
    foreach ($prop in $item.PSObject.Properties) {
        if (-not $allProperties.ContainsKey($prop.Name)) {
            # 判断是否为嵌套对象，嵌套对象列类型设为String
            $isNested = $prop.Value -is [System.Management.Automation.PSCustomObject]
            $colType = if ($isNested) { [string] } else { $prop.TypeNameOfValue -as [type] }
            # 处理无法解析的类型，默认设为String
            if (-not $colType) { $colType = [string] }
            $allProperties[$prop.Name] = $colType
        }
    }
}

# 第二步：根据预定义的类型创建所有列
foreach ($propName in $allProperties.Keys) {
    $col = New-Object System.Data.DataColumn($propName, $allProperties[$propName])
    $manipulatedData.Columns.Add($col) | Out-Null
}

# 第三步：填充数据到DataTable
$importedData | ForEach-Object {
    $newRow = $manipulatedData.NewRow()
    foreach ($prop in $_.PSObject.Properties) {
        $propName = $prop.Name
        if ($allProperties[$propName] -eq [string] -and $prop.Value -is [System.Management.Automation.PSCustomObject]) {
            # 仅将嵌套对象的Value转为XML字符串，添加-NoTypeInformation减少冗余
            $xml = $prop.Value | ConvertTo-Xml -As String -NoTypeInformation
            $newRow.$propName = $xml
        } else {
            # 基础类型直接赋值，处理空值和无效内容
            if ($prop.Value -ne $null -and $prop.Value -ne '{}') {
                $newRow.$propName = $prop.Value
            }
        }
    }
    $manipulatedData.Rows.Add($newRow)
}

额外优化点 #

• 添加-NoTypeInformation参数精简XML内容，减少存储空间
• 增加无法解析类型的兜底处理，避免后续SQL导入报错
• 提前定义列类型彻底解决自动推断导致的类型冲突问题

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