Home > Net >  Parsing JSON string using OPENJSON giving error despite a valid JSON is provided
Parsing JSON string using OPENJSON giving error despite a valid JSON is provided

Time:10-07

I am trying to parse a JSON string using SQL in SQL Server. Below is the JSON string.

 DECLARE @json nvarchar(max)
 SET @json=
'{"value":[
           {
           "branch code": "2010",
           "rfq type": 1,
           "created": "20220930 12:31:39",
           "from": "MUNDRA",
           "to": "BANGALURU",
           "vehicle gate in time": "20220930 12:34:55",
           "GROSSWT": "",
           "do accepted date": "20220930 12:31:39",
           "indent type": 1,
           "indent number": "446/1",
           "invoice data": "",
           "is milk run": false,
           "do weight": ""}]}'

I am using below query to parse this but getting error:

    SELECT  j1.[key] AS ColumnName
    --,CAST(j1.value AS int) AS LastUpdatedValue
    FROM OPENJSON(@json, '$.value') j1
    --CROSS APPLY OPENJSON(j1.value) j2

The error I am getting as below:

   Msg 13609, Level 16, State 4, Line 21
   JSON text is not properly formatted. Unexpected character ' ' is found at position 
   13.

As the msg suggest, there is an issue with JSON text. But I have checked in enter image description here

In fact in my cas I have a char UNICODE u200B / #8203 non printable that induce a fine mess !

Replace manually all non printable chars by nothing or spaces.

  • Related