I am trying to extract values in PLSQL from json response which came from SOAP API response, JSON response:

{
    "@xmlns:SOAP-ENV": "http://test",
    "SOAP-ENV:Header": null,
    "SOAP-ENV:Body": {
      "ProcessShipmentReply": {
        "@xmlns": "http://test.com/",
        "HighestSeverity": "WARNING",
        "Notifications": {
          "Severity": "WARNING",
          "Source": "ship",
          "Code": "7033"
        },
        "TransactionDetail": {
          "CustomerTransactionId": "12345655"
        },
        "Version": {
          "ServiceId": "ship",
          "Major": "28",
          "Intermediate": "0",
          "Minor": "0"
        },
        "JobId": "1231561",
        "RequiredDetail": {
          "UsDomestic": "false",
          "CarrierCode": "test",
          "MasterTrackingId": {
            "TrackingIdType": "test",
            "FormId": "0430",
            "TrackingNumber": "456413421"
          }
         
        }
      }
    }
  }

PLSQL by which I am trying to extract values from json:

APEX_JSON.parse(RESP);
P_TRACK_NO := APEX_JSON.get_varchar2('SOAP-ENV:Body.ProcessShipmentReply.RequiredDetail.MasterTrackingId.TrackingNumber');

I have done this many times for other API response and doing the same with this but I think due to SOAP API response I am unable to get the same results as I am getting for other API. Please suggest me, how can I extract values from this JSON response.

CodePudding user response：

I suppose the ":" in the json key "SOAP-ENV:Body" is throwing it off so best to enclose it in double qoutes:

SET SERVEROUTPUT ON
DECLARE
  l_json_text VARCHAR2(32767);
  l_json_values    apex_json.t_values;

BEGIN

l_json_text := '
{
    "@xmlns:SOAP-ENV": "http://test",
    "SOAP-ENV:Header": null,
    "SOAP-ENV:Body": {
      "ProcessShipmentReply": {
        "@xmlns": "http://test.com/",
        "HighestSeverity": "WARNING",
        "Notifications": {
          "Severity": "WARNING",
          "Source": "ship",
          "Code": "7033"
        },
        "TransactionDetail": {
          "CustomerTransactionId": "12345655"
        },
        "Version": {
          "ServiceId": "ship",
          "Major": "28",
          "Intermediate": "0",
          "Minor": "0"
        },
        "JobId": "1231561",
        "RequiredDetail": {
          "UsDomestic": "false",
          "CarrierCode": "test",
          "MasterTrackingId": {
            "TrackingIdType": "test",
            "FormId": "0430",
            "TrackingNumber": "456413421"
          }
         
        }
      }
    }
  }

';

  apex_json.parse(
    p_values => l_json_values,
    p_source => l_json_text
  );

  DBMS_OUTPUT.put_line('----------------------------------------'); 
  if apex_json.does_exist(p_path => '"SOAP-ENV:Body".ProcessShipmentReply.RequiredDetail.MasterTrackingId.TrackingNumber',p_values => l_json_values) then 
  dbms_output.put_line(apex_json.get_number(p_path => '"SOAP-ENV:Body".ProcessShipmentReply.RequiredDetail.MasterTrackingId.TrackingNumber', p_values => l_json_values));
  end if; 
  DBMS_OUTPUT.put_line('----------------------------------------'); 
END;
/

----------------------------------------
456413421
----------------------------------------


PL/SQL procedure successfully completed.