System.Data.SqlClient.SqlException
HResult=0x80131904
Message=JSON text is not properly formatted. Unexpected character ']'
is found at position 501.
Source=Core .Net SqlClient Data Provider
這是 C# 代碼：
using (SqlConnection conn = new SqlConnection(connString))
{
conn.Open();
using (SqlCommand cmd = new SqlCommand(sprocname, conn))
{
// Set command object as a stored procedure
cmd.CommandType = CommandType.StoredProcedure;
// Add parameter that will be passed to stored procedure
cmd.Parameters.Add(new SqlParameter(paramName, paramValue));
cmd.ExecuteReader();
}
}
uj5u.com熱心網友回復：
這是因為您的 JSON 有多個根節點，因此 SQL Server 只選擇第一個。我們可以通過以下方式看到這一點：
DECLARE @JSON nvarchar(MAX) = N'{
"GLDETAIL": {
"RECORDNO": "264378-1756289-919567--accrual",
"BATCH_DATE": "02/01/2022"
}
},
{
"GLDETAIL": {
"RECORDNO": "264378-1756290-919568--accrual",
"BATCH_DATE": "02/01/2022"
}
}';
SELECT *
FROM OPENJSON(@JSON)
請注意，對于 GLDETAIL，只拾取一組，而不是 2。
我們可以通過將資料放入陣列中來“解決”這個問題：
DECLARE @JSON nvarchar(MAX) = N'{
"GLDETAIL": {
"RECORDNO": "264378-1756289-919567--accrual",
"BATCH_DATE": "02/01/2022"
}
},
{
"GLDETAIL": {
"RECORDNO": "264378-1756290-919568--accrual",
"BATCH_DATE": "02/01/2022"
}
}
,
{
"GLDETAIL": {
"RECORDNO": "264379-1756291-919569--accrual",
"BATCH_DATE": "02/01/2022"
}
},
{
"GLDETAIL": {
"RECORDNO": "264379-1756292-919570--accrual",
"BATCH_DATE": "02/01/2022"
}
}';
SELECT GLD.RECORDNO,
GLD.BATCH_DATE
FROM OPENJSON(CONCAT('[',@JSON,']')) OJ
CROSS APPLY OPENJSON(OJ.[value],'$.GLDETAIL')
WITH (RECORDNO varchar(30),--USe an appropriate length, not MAX
BATCH_DATE date) GLD;
轉載請註明出處，本文鏈接：https://www.uj5u.com/qukuanlian/468699.html