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

Question

我试图从json响应中提取PLSQL中的值，该响应来自SOAP API响应，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响应中提取值。

Answer 1

我猜是";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.