FSM Connect - Handle JSON Transaction Result

Hi,

I am looking at a process where I am doing a GET to obtain some data from a 3rd party application.

The following is the result I am getting.

{
   "pageNumber":0,
   "pageSize":25,
   "totalElements":1,
   "content":o
      {
         "id":"e6wKBQFEAjIAAAGPHsYAe8jW",
         "orderNumber":"MK100003652",
         "supplierNumber":"VEUR0393",
         "referenceNumber":"3103704",
         "orderStatus":"ordered",
         "completelyInvoiced":false,
         "processStatus":"open",
         "lastModified":"2024-05-30T09:31:09.000Z"
      }
   ]
}

 

I am from that wanting to use the values in the content for orderNumber and referenceNumber. Where the referenceNumber is our purchase_order_id and I want to put orderNumber into user_def1 on Purchase Order table.

In the past when dealing with JSON I have used a business rule and a <perform_exec_db_edit> XML with the SQL update doing the leg work.

I have tried the same with this process but it’s returning an exception when using that perform. The SQL itself is sound and can be used independently through SSMS, but the XML post is bringing back an exception.

The SQL is;

<perform_exec_db_edit>
  <parameters>
    <sql_command>
declare @json nvarchar(max)= (select transaction_result from integration_log_detail where run_id = '21384551')

SELECT *
INTO  #jsondata
FROM OPENJSON (@json, '$.content' ) 
WITH ( orderNumber] NVARCHAR(25) '$.orderNumber', treferenceNumber] NVARCHAR(25) '$.referenceNumber')
-- select * from #jsondata
 
update purchase_order
set user_def1 = #jsondata.borderNumber]
from #jsondata

where purchase_order_id = #jsondata.ereferenceNumber]

drop table #jsondata
 </sql_command>
  </parameters>
</perform_exec_db_edit>

Again that SQL is spot on, it does the job. However exception it returns is;

      <application_error>
        <severity>ERROR</severity>
        <message>Error:Incorrect syntax near (. at line nr:6 column:6 </message>
      </application_error>

So my question is;

Is there a better way to grab data from the transaction result and use it to update tables in FSM?

Had to look at alternative solutions on the past for JSON, but this one is causing me some issues.

Thanks as always

 

Ady