PLSQL XML code notesPrimary tabs

Submitted by code_admin on Fri, 07/20/2018 - 12:47

Output pretty xml

  1.   select XMLSERIALIZE(Document l_msg as CLOB INDENT SIZE = 2)
  2.   into l_tmp
  3.   from dual;
  4.   dbms_output.put_line(l_tmp);

Loop through xml results

  1.  /* LOOP through the Results*/
  2.  l_result_cnt := 1;
  3.  WHILE l_xmlresp.existsNode('//AccessReq:QueryMessage/AccessReq:body/AccessReq:Results/AccessReq:Result[' || to_char(l_result_cnt) || ']',v_ns_map) = 1
  4.  LOOP
  5.    SELECT extract(l_xmlresp, '//AccessReq:QueryMessage/AccessReq:body/AccessReq:Results/AccessReq:Result[' || to_char(l_result_cnt) || ']', v_ns_map)
  6.       INTO l_xmlsingleresult
  7.    FROM dual;
  8.  
  9.    
  10.    l_result_cnt := l_result_cnt + 1;
  11.  end loop;

Get some data

  1.    SELECT
  2.     extractvalue(l_xmlsingleresult, '/AccessReq:Result/AccessReq:Identifier', v_ns_map),
  3.     extractvalue(l_xmlsingleresult, '/AccessReq:Result/AccessReq:SystemName', v_ns_map),
  4.     extractvalue(l_xmlsingleresult, '/AccessReq:Result/AccessReq:Action', v_ns_map),
  5.     extractvalue(l_xmlsingleresult, '/AccessReq:Result/AccessReq:User/AccessReq:Identifier', v_ns_map),
  6.     extractvalue(l_xmlsingleresult, '/AccessReq:Result/AccessReq:Role/AccessReq:Identifier', v_ns_map),
  7.     extractvalue(l_xmlsingleresult, '/AccessReq:Result/AccessReq:OriginatingRequest/AccessReq:Identifier', v_ns_map),
  8.     extractvalue(l_xmlsingleresult, '/AccessReq:Result/AccessReq:OriginatingRequest/AccessReq:System', v_ns_map)
  9.    INTO
  10.     l_identifier,
  11.     l_systemName,
  12.     l_Action,
  13.     l_User,
  14.     l_Role,
  15.     l_OriginatingReqID,
  16.     l_OriginatingReqSys
  17.    FROM dual;

Outputting XML

Convert VARCHAR2 into XML Safe VARCHAR2

HTF.ESCAPE_SC(l_str)

Copied from other page

  1. set serveroutput on
  2. DECLARE
  3. resp XMLTYPE := XMLTYPE('<?xml version="1.0" encoding="UTF-8"?>
  4. <soapenv:Envelope xmlns:soapenv="http://www.w3.org/2003/05/soap-envelope"&gt;
  5. <soapenv:Body><if:sayGreetingsResponse xmlns:if="http://xml.bnz.co.nz/filetransferadministrationservice/TestIF"&gt;
  6. <if:output>Hello Bhooma. Thank you for using this service.</if:output></if:sayGreetingsResponse></soapenv:Body>
  7. </soapenv:Envelope>');
  8.  
  9. xout varchar2(300);
  10. BEGIN
  11. SELECT extractvalue( resp
  12. , '/soap:Envelope/soap:Body/if:sayGreetingsResponse/if:output'
  14. INTO xout
  15. FROM dual;
  16.  
  17. dbms_output.put_line('### '||xout||' ###');
  18.  
  19.  
  20. END;
  21. /

XMLElement to produce safe XML

  1.     select
  2.       xmlelement("msg",
  3.         xmlattributes(  'SOMENAMESPACE' as "xmlns"),
  4.         xmlelement("resourse_name",p_resourse_name),
  5.         xmlelement("resourse_id",p_resourse_id),
  6.         xmlelement("operations",p_operations),
  7.         xmlelement("num_unbatched_events",p_num_unbatched_events),
  8.         xmlelement("payload",p_payload)
  9.       )
  10.     into x_xml
  11.     from dual;

XMLForest to produce safe XML

  1.     select xmlforest(xmlforest(
  2.       p_resourse_name as "resourse_name",
  3.       p_resourse_id as "resourse_id",
  4.       p_operations as "operations",
  5.       p_num_unbatched_events as "num_unbatched_events",
  6.       p_payload as "payload"
  7.     ) as "msg")
  8.     into x_xml
  9.     from dual;

Tags

RJM Article Type
Quick Reference