Friday 14 June 2013

xml dom functions explanation with examples in plsql.


xml functions:
======================


DBMS_XMLGEN.newContext(query);

---Create new Context for the Query

example:
=====================
 v_ctx                     dbms_xmlgen.ctxHandle;
 v_mem_xml                 XMLTYPE;

v_mem_select VARCHAR2(4000):='select EFFECTIVE_TO_DATE,INCEPTION_DATE,MEM_STATUS,EFFECTIVE_FROM_DATE  FROM vw_rule_member WHERE member_seq_id =17541428';

v_ctx := dbms_xmlgen.newContext(v_mem_select);

*****************************************************************************************************************


dbms_xmlgen.setRowSetTag(v_ctx,'fielddata');

--it will set rowset as fielddata

example:
===================
before:
--------

<ROWSET>
 <ROW>
  <EFFECTIVE_TO_DATE>31/03/2014</EFFECTIVE_TO_DATE>
  <INCEPTION_DATE>01/04/2013</INCEPTION_DATE>
  <MEM_STATUS>POA</MEM_STATUS>
  <EFFECTIVE_FROM_DATE>01/04/2013</EFFECTIVE_FROM_DATE>
 </ROW>
</ROWSET>


declare

 v_ctx                     dbms_xmlgen.ctxHandle;
 v_mem_xml                 XMLTYPE;
begin

v_mem_select VARCHAR2(4000):='select EFFECTIVE_TO_DATE,INCEPTION_DATE,MEM_STATUS,EFFECTIVE_FROM_DATE  FROM vw_rule_member WHERE member_seq_id =17541428';

v_ctx := dbms_xmlgen.newContext(v_mem_select);

dbms_xmlgen.setRowSetTag(v_ctx,'fielddata');
  v_mem_xml := DBMS_XMLGEN.GetXMLTYPE(v_ctx);

end;



after:
---------

<fielddata>
 <ROW>
  <EFFECTIVE_TO_DATE>31/03/2014</EFFECTIVE_TO_DATE>
  <INCEPTION_DATE>01/04/2013</INCEPTION_DATE>
  <MEM_STATUS>POA</MEM_STATUS>
  <EFFECTIVE_FROM_DATE>01/04/2013</EFFECTIVE_FROM_DATE>
 </ROW>
</fielddata>

***************************************************************************************************


  dbms_xmlgen.setRowTag(v_ctx,'MEMBER');
--it will set row as MEMBER

example:
======================

before:
---------

<ROWSET>
 <ROW>
  <EFFECTIVE_TO_DATE>31/03/2014</EFFECTIVE_TO_DATE>
  <INCEPTION_DATE>01/04/2013</INCEPTION_DATE>
  <MEM_STATUS>POA</MEM_STATUS>
  <EFFECTIVE_FROM_DATE>01/04/2013</EFFECTIVE_FROM_DATE>
 </ROW>
</ROWSET>


declare
 v_ctx                     dbms_xmlgen.ctxHandle;
 v_mem_xml                 XMLTYPE;
begin

v_mem_select VARCHAR2(4000):='select EFFECTIVE_TO_DATE,INCEPTION_DATE,MEM_STATUS,EFFECTIVE_FROM_DATE  FROM vw_rule_member WHERE member_seq_id =17541428';

v_ctx := dbms_xmlgen.newContext(v_mem_select);

dbms_xmlgen.setRowTag(v_ctx,'MEMBER');
  v_mem_xml := DBMS_XMLGEN.GetXMLTYPE(v_ctx);
end;


after:
----------

<ROWSET>
 <MEMBER>
  <EFFECTIVE_TO_DATE>31/03/2014</EFFECTIVE_TO_DATE>
  <INCEPTION_DATE>01/04/2013</INCEPTION_DATE>
  <MEM_STATUS>POA</MEM_STATUS>
  <EFFECTIVE_FROM_DATE>01/04/2013</EFFECTIVE_FROM_DATE>
 </MEMBER>

</ROWSET>

*****************************************************************************************************************
dbms_xmldom.importNode(doc ,importednode ,'true/false');

--If set to false, only the node itself is imported, if set to true, all child nodes (descendants)
-- are also imported
IT WILL IMPORT NODE VALUE TO DOM DOCUMENT  THEN WE WILL APPEND THE NODE DATA TO DOM DOCUMENT

v_req_root_node := dbms_xmldom.importNode(v_mem_doc,v_req_root_node,TRUE);
   v_mem_root_node := dbms_xmldom.appendChild(v_mem_root_node,v_req_root_node);
   v_field_xml := dbms_xmldom.getxmltype(v_mem_doc);

WITHOUT USING IMPORT NODE FUNCTION YOU CANNOT APPEND ONE NODE TO ANOTHER NODE.


example:
===============================

before:
----------------

<fielddata>
  <MEMBER>
    <EFFECTIVE_TO_DATE>31/03/2014</EFFECTIVE_TO_DATE>
    <INCEPTION_DATE>01/04/2013</INCEPTION_DATE>
    <MEM_STATUS>POA</MEM_STATUS>
    <EFFECTIVE_FROM_DATE>01/04/2013</EFFECTIVE_FROM_DATE>
  </MEMBER>
</fielddata>

declare
 v_mem_doc                 DBMS_XMLDOM.DOMDocument;
    v_req_root_node           DBMS_XMLDOM.DOMNode;
    v_mem_root_node           DBMS_XMLDOM.DOMNode;
v_dom_ele dbms_xmldom.DOMElement;
 v_mem_xml                 XMLTYPE;
v_buff  varchar2(20000);

begin

v_mem_xml    :=xmltype('<fielddata>
 <MEMBER>
  <EFFECTIVE_TO_DATE>31/03/2014</EFFECTIVE_TO_DATE>
  <INCEPTION_DATE>01/04/2013</INCEPTION_DATE>
  <MEM_STATUS>POA</MEM_STATUS>
  <EFFECTIVE_FROM_DATE>01/04/2013</EFFECTIVE_FROM_DATE>
 </MEMBER>
</fielddata>
');

 v_mem_doc := DBMS_XMLDOM.newDOMDocument(v_mem_xml);
   v_mem_root_node := DBMS_XMLDOM.makeNode(DBMS_XMLDOM.getDocumentElement((v_mem_doc)));
dbms_xmldom.writeToBuffer(v_mem_root_node,v_buff);
dbms_output.put_line(v_buff);
 
   v_mem_root_node := dbms_xmldom.importNode(v_mem_doc,v_mem_root_node,true);

dbms_xmldom.writeToBuffer(v_mem_root_node,v_buff);
dbms_output.put_line(v_buff);
end;

after:
-------------


<fielddata>
  <MEMBER>
    <EFFECTIVE_TO_DATE>31/03/2014</EFFECTIVE_TO_DATE>
    <INCEPTION_DATE>01/04/2013</INCEPTION_DATE>
    <MEM_STATUS>POA</MEM_STATUS>
    <EFFECTIVE_FROM_DATE>01/04/2013</EFFECTIVE_FROM_DATE>
  </MEMBER>
</fielddata>



before:
----------------

<fielddata>
  <MEMBER>
    <EFFECTIVE_TO_DATE>31/03/2014</EFFECTIVE_TO_DATE>
    <INCEPTION_DATE>01/04/2013</INCEPTION_DATE>
    <MEM_STATUS>POA</MEM_STATUS>
    <EFFECTIVE_FROM_DATE>01/04/2013</EFFECTIVE_FROM_DATE>
  </MEMBER>
</fielddata>

declare
 v_mem_doc                 DBMS_XMLDOM.DOMDocument;
    v_req_root_node           DBMS_XMLDOM.DOMNode;
    v_mem_root_node           DBMS_XMLDOM.DOMNode;
v_dom_ele dbms_xmldom.DOMElement;
 v_mem_xml                 XMLTYPE;
v_buff  varchar2(20000);

begin

v_mem_xml    :=xmltype('<fielddata>
 <MEMBER>
  <EFFECTIVE_TO_DATE>31/03/2014</EFFECTIVE_TO_DATE>
  <INCEPTION_DATE>01/04/2013</INCEPTION_DATE>
  <MEM_STATUS>POA</MEM_STATUS>
  <EFFECTIVE_FROM_DATE>01/04/2013</EFFECTIVE_FROM_DATE>
 </MEMBER>
</fielddata>
');

 v_mem_doc := DBMS_XMLDOM.newDOMDocument(v_mem_xml);
   v_mem_root_node := DBMS_XMLDOM.makeNode(DBMS_XMLDOM.getDocumentElement((v_mem_doc)));
dbms_xmldom.writeToBuffer(v_mem_root_node,v_buff);
dbms_output.put_line(v_buff);
 
   v_mem_root_node := dbms_xmldom.importNode(v_mem_doc,v_mem_root_node,false);

dbms_xmldom.writeToBuffer(v_mem_root_node,v_buff);
dbms_output.put_line(v_buff);
end;

after:
-------------


<fielddata/>



***************************************************************************

DBMS_XMLDOM.getDocumentElement;

--it will return rool element for xml
Example:
---------------\
Before:

<fielddata>
 <MEMBER>
  <EFFECTIVE_TO_DATE>31/03/2014</EFFECTIVE_TO_DATE>
  <INCEPTION_DATE>01/04/2013</INCEPTION_DATE>
  <MEM_STATUS>POA</MEM_STATUS>
  <EFFECTIVE_FROM_DATE>01/04/2013</EFFECTIVE_FROM_DATE>
 </MEMBER>
</fielddata>

declare
 v_mem_doc                 DBMS_XMLDOM.DOMDocument;
    v_req_root_node           DBMS_XMLDOM.DOMNode;
    v_mem_root_node           DBMS_XMLDOM.DOMNode;
v_dom_ele dbms_xmldom.DOMElement;
 v_mem_xml                 XMLTYPE;
v_buff  varchar2(20000);

begin

v_mem_xml    :=xmltype('<fielddata>
 <MEMBER>
  <EFFECTIVE_TO_DATE>31/03/2014</EFFECTIVE_TO_DATE>
  <INCEPTION_DATE>01/04/2013</INCEPTION_DATE>
  <MEM_STATUS>POA</MEM_STATUS>
  <EFFECTIVE_FROM_DATE>01/04/2013</EFFECTIVE_FROM_DATE>
 </MEMBER>
</fielddata>
');

 v_mem_doc := DBMS_XMLDOM.newDOMDocument(v_mem_xml);
v_dom_ele:=DBMS_XMLDOM.getDocumentElement((v_mem_doc));
end;
   v_mem_root_node := DBMS_XMLDOM.makeNode(DBMS_XMLDOM.getDocumentElement((v_mem_doc)));

dbms_xmldom.writeToBuffer(v_mem_root_node,v_buff);
dbms_output.put_line(v_buff);

after:

<fielddata>
 <MEMBER>
  <EFFECTIVE_TO_DATE>31/03/2014</EFFECTIVE_TO_DATE>
  <INCEPTION_DATE>01/04/2013</INCEPTION_DATE>
  <MEM_STATUS>POA</MEM_STATUS>
  <EFFECTIVE_FROM_DATE>01/04/2013</EFFECTIVE_FROM_DATE>
 </MEMBER>
</fielddata>

Posted by at No comments:
Subscribe to: Posts (Atom)