Oracle JSON数据中的XML

ASKTOM 2020-12-16

292

问题描述

嗨，汤姆，

我们正在以JSON的形式在数据库中准备请求。

v_http_request CLOB;
v_req_body CLOB;

 v_http_request := '{"url":"' || www.oracle.com|| 
                       '","message_id":"' || 1234|| 
                       '","header":"' ||Content-Type#text/xml; charset=UTF-8;Content-Length#872 || 
                       '","body_char_set":"' || UTF-8 ||
                       '","timeout":"' || 60 || 
                       '","proxy_url":"' || null||
                       '","http_method":"' || POST ||
                       '","http_version":"' || 1.1 ||
                       '","http_req_body":"' || v_req_body|| 
                      '"}';复制

和req_body包含XML消息。


  
  
    .....
  
复制

我们面临的问题是req主体是XML，它在和JSON解析器之间有 “” 给出的问题。
并且没有方法，我们看到转换或应用编码和解码的CLOB列。

和q'[req_body] -报价仅适用于32767字符。

您能否建议如何处理这种情况。

谢谢

专家解答

您可以尝试:

- Converting problematic characters to their HTML code (例如。 double-quotes => ") OR
-base64对XML文档进行编码

例如。

set define off
declare
  jdoc varchar2(4000);
  xdoc xmltype;
begin
  xdoc := xmltype ( q'!
  
  
  
!' );

  jdoc :=
    ' { "url " : "www.oracle.com" , "message_id" : 1234, "xml":"' || 
    replace (
      replace ( 
        replace ( 
          replace (
            xdoc.getStringVal(), 
            '"', '"' 
          ), '/', '/'
        ), '<', '<'
      ), '>', '>'
    ) || '" }';
  
  dbms_output.put_line ( jdoc );

  jdoc := 
    ' { "url " : "www.oracle.com" , "message_id" : 1234, "xml":"' || 
    utl_raw.cast_to_varchar2 (
      utl_encode.base64_encode ( 
        utl_raw.cast_to_raw ( 
          xdoc.getStringVal() 
        )
      )
    ) || '", "encoding" : "base64" }';
    
  dbms_output.put_line ( jdoc );
end;
/

{ "url " : "www.oracle.com" , "message_id" : 1234, "xml":"<soapenv:Envelope xmlns:soapenv="http://schemas.xmlsoap.org/soap/envelope/">
  <soapenv:Header/>
  <soapenv:Body>
  </soapenv:Body>
</soapenv:Envelope>" }
 { "url " : "www.oracle.com" , "message_id" : 1234, "xml":"PHNvYXBlbnY6RW52ZWxvcGUgeG1sbnM6c29hcGVudj0iaHR0cDovL3NjaGVtYXMu
eG1sc29hcC5vcmcvc29hcC9lbnZlbG9wZS8iPgogIDxzb2FwZW52OkhlYWRlci8+
CiAgPHNvYXBlbnY6Qm9keT4KICA8L3NvYXBlbnY6Qm9keT4KPC9zb2FwZW52OkVu
dmVsb3BlPg==", "encoding" : "base64" }复制