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>