1

SQL proc consuming JSON payload from Web Service but the data was escaped before being received in the procedure. I have a payload like this:

{
    "RES_NBR_ID": "00000056566",
    "RES_SCRIPT_NM": "Test Script",
    "RES_SCRIPT_ID": "jcet2",
    "RES_RESULT_GROUP_ID": 42622528,
    "RES_CREATED_BY_ID": "Jcccc",
    "RES_QUESTION_TXT": "asdf",
    "RES_ANSWER_TXT": "asdfa\"asfasf",
    "SORT_NBR": 4,
    "RES_QUESTION_ID": 4,
    "RES_DELETED_BY_ID": ""
}

You'll notice that the ANSWER_TXT is really asdfa"asfasf The backslash was put in by the JSON.stringify function on the service

So will using OPENJSON like the following remove all stringfy sequences:

SELECT (SELECT MAX(RES_ID) FROM SCRT_RESULTS) + ROW_NUMBER() OVER(ORDER BY RES_QUESTION_TXT) AS RES_ID
,RES_NBR_ID
,RES_SCRIPT_NM
,RES_QUESTION_TXT
,RES_ANSWER_TXT
,RES_CREATED_BY_ID
,getdate() [RES_CREATED_DT]
,RES_DELETED_BY_ID
,getdate() [RES_DELETED_DT]
,RES_RESULT_GROUP_ID
,RES_SORT_NBR
,RES_SCRIPT_ID
,RES_QUESTION_ID
,getUTCdate() [RES_CREATED_UTC_DT]
 from openjson(@json_result) 
WITH(
     RES_NBR_ID     varchar(16)
    ,RES_SCRIPT_NM          varchar(150) 
    ,RES_SCRIPT_ID          varchar(5) 
    ,RES_RESULT_GROUP_ID    bigint 
    ,RES_CREATED_BY_ID      varchar(20)  
    ,RES_QUESTION_TXT       varchar(2000)  
    ,RES_ANSWER_TXT         varchar(2000) 
    ,RES_SORT_NBR           int 
    ,RES_QUESTION_ID        int 
    ,RES_DELETED_BY_ID      varchar(20) 

 )
user3297833
  • 141
  • 2
  • 9

0 Answers0