使用OPENJSON向临时表插入JSON数据仅返回空行，求技术支持

核心问题 #

1. OPENJSON路径错误：你的JSON结构外层包含BudgetList数组，直接使用OPENJSON (@XmlStringNPDBudget)只会解析外层对象，返回的是BudgetList这个数组字段，而非数组内的每个预算对象，因此查询不到有效数据，返回NULL行。
2. JSON类型不匹配：数组中第9个元素的Year和Budget是字符串类型（"2022"、"11111"），与目标表的INT、DECIMAL类型虽能隐式转换，但存在潜在报错风险。

修复后的代码 #

DECLARE @XmlStringNPDBudget NVARCHAR(max) = '{"BudgetList":[{"BudgetId":4,"Month":1,"Year":2022,"Budget":750000},{"BudgetId":5,"Month":2,"Year":2022,"Budget":950000},{"BudgetId":0,"Month":3,"Year":0,"Budget":0},{"BudgetId":0,"Month":4,"Year":0,"Budget":0},{"BudgetId":0,"Month":5,"Year":0,"Budget":0},{"BudgetId":0,"Month":6,"Year":0,"Budget":0},{"BudgetId":0,"Month":7,"Year":0,"Budget":0},{"BudgetId":0,"Month":8,"Year":0,"Budget":0},{"BudgetId":0,"Month":9,"Year":"2022","Budget":"11111"},{"BudgetId":1,"Month":10,"Year":2022,"Budget":1000},{"BudgetId":2,"Month":11,"Year":2022,"Budget":350000},{"BudgetId":3,"Month":12,"Year":2022,"Budget":550000}]}'

DROP TABLE IF EXISTS #ParticularYearBudgetInsertUpdate

CREATE TABLE #ParticularYearBudgetInsertUpdate 
(
    [BudgetId] INT, 
    [Month] INT,
    [Year] INT,
    [Budget] DECIMAL
) 

-- 修正1：指定OPENJSON解析BudgetList数组路径
-- 修正2：对字符串类型的字段显式转换，避免隐式转换潜在问题
INSERT INTO #ParticularYearBudgetInsertUpdate([BudgetId], [Month], [Year], [Budget])
    SELECT 
        [BudgetId], 
        [Month],
        -- 显式转换字符串类型的Year为INT，转换失败返回NULL
        TRY_CAST([Year] AS INT) AS [Year],
        -- 显式转换字符串类型的Budget为DECIMAL，转换失败返回NULL
        TRY_CAST([Budget] AS DECIMAL) AS [Budget]
    FROM 
        OPENJSON (@XmlStringNPDBudget, '$.BudgetList')
        WITH (
            BudgetId INT, 
            [Month] INT,
            [Year] NVARCHAR(10), -- 先按字符串读取，再转换
            [Budget] NVARCHAR(20) -- 先按字符串读取，再转换
        )

SELECT * FROM #ParticularYearBudgetInsertUpdate

SELECT * FROM NPDBudget

关键修改说明 #

• 定位JSON数组：OPENJSON (@XmlStringNPDBudget, '$.BudgetList') 直接指向数组节点，确保能遍历到每个预算对象。
• 安全类型转换：用TRY_CAST处理可能的字符串转数值场景，即使转换失败也不会中断执行，可根据业务需求调整为强转并添加错误处理逻辑。

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