apex_json.get_varchar2未从SOAP API的json响应中提取值

yqlxgs2m  于 2023-01-03  发布在  其他
关注(0)|答案(1)|浏览(126)

我正在尝试从来自SOAP API响应的JSON响应中提取PLSQL中的值,JSON响应:

{
    "@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"
          }
         
        }
      }
    }
  }

我尝试从json中提取值的PLSQL:

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

我已经为其他API响应做过很多次了,也对这个做过同样的事情,但是我认为由于SOAP API响应,我无法得到与其他API相同的结果。请给我建议,我如何从这个JSON响应中提取值。

bgibtngc

bgibtngc1#

我猜想json键“SOAP-ENV:Body”中的“:“会将其丢弃,因此最好将其包含在双引号中:

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.

相关问题