How to insert multiple JSON rows into a T-SQL table-CodePudding

The

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

This is C# code:

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(); } }

CodePudding user response：

This is because your JSON has multiple root nodes, and so SQL Server is only picking up the first. We can see this with the following:

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)

Notice only one set is picked up, for GLDETAIL, not 2.

We can "fix" this by making the data inside an array:

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;