Oracle SQL & PL/SQL : How to Parse XML using DOM Parser in Oracle

SET DEFINE OFF;
SET SERVEROUTPUT ON;

DECLARE
v_mds_xml_parser DBMS_XMLPARSER.parser;
v_mds_xml_doc DBMS_XMLDOM.domdocument;
v_Release_node xmldom.domnode;
node_not_exists EXCEPTION;
v_ShipToLocationRef_list xmldom.domnodelist;
v_ShipToLocationRef_node xmldom.domnode;
v_LocationRef_list xmldom.domnodelist;
v_LocationRef_node xmldom.domnode;
v_Location_list xmldom.domnodelist;
v_Location_node xmldom.domnode;
v_TransactionCode VARCHAR2 (1000) := NULL;
v_LocationGid_list xmldom.domnodelist;
v_LocationGid_node xmldom.domnode;
v_Gid_list xmldom.domnodelist;
v_Gid_node xmldom.domnode;
v_DomainName VARCHAR2 (1000) := NULL;
v_Xid VARCHAR2 (1000) := NULL;
v_LocationName VARCHAR2 (1000) := NULL;
v_Address_list xmldom.domnodelist;
v_Address_node xmldom.domnode;
v_AddressLine1 VARCHAR2 (100) := NULL;
v_AddressLine2 VARCHAR2 (100) := NULL;
v_City VARCHAR2 (100) := NULL;
v_Province VARCHAR2 (100) := NULL;
v_PostalCode VARCHAR2 (100) := NULL;
v_CountryCode_list xmldom.domnodelist;
v_CountryCode_node xmldom.domnode;
v_CountryCode VARCHAR2 (100) := NULL;

reqxml CLOB
:= '<sample:Release xmlns:sample="http://xmlns.oracle.com/apps/sample/transmission/v6.4" xmlns:gtm="http://xmlns.oracle.com/apps/gtm/transmission/v6.4">
<sample:ReleaseGid>
<sample:Gid>
<sample:DomainName>ABC</sample:DomainName>
<sample:Xid>1234</sample:Xid>
</sample:Gid>
</sample:ReleaseGid>
<sample:TransactionCode>CR</sample:TransactionCode>
<sample:ShipToLocationRef>
<sample:LocationRef>
<sample:Location>
<sample:TransactionCode>KK</sample:TransactionCode>
<sample:LocationGid>
<sample:Gid>
<sample:DomainName>ABC</sample:DomainName>
<sample:Xid>87901111</sample:Xid>
</sample:Gid>
</sample:LocationGid>
<sample:LocationName>MADIWALA</sample:LocationName>
<sample:Address>
<sample:AddressLine1>41, BTM LAYOUT</sample:AddressLine1>
<sample:AddressLine2/>
<sample:City>BANGALORE</sample:City>
<sample:Province/>
<sample:PostalCode>560076</sample:PostalCode>
<sample:CountryCode>
<sample:CountryCode2>KR</sample:CountryCode2>
</sample:CountryCode>
</sample:Address>
<sample:Contact>
<sample:ContactGid>
<sample:Gid>
<sample:DomainName>ABC</sample:DomainName>
<sample:Xid>87901111</sample:Xid>
</sample:Gid>
</sample:ContactGid>
<sample:IsPrimaryContact>Y</sample:IsPrimaryContact>
</sample:Contact>
<sample:LocationRole>
<sample:LocationRoleGid>
<sample:Gid>
<sample:DomainName>PUBLIC</sample:DomainName>
<sample:Xid>SHIPFROM/SHIPTO</sample:Xid>
</sample:Gid>
</sample:LocationRoleGid>
</sample:LocationRole>
</sample:Location>
</sample:LocationRef>
</sample:ShipToLocationRef>
</sample:Release>';

FUNCTION f_verify_and_gettag_value (i_elem xmldom.domelement,
i_tagname VARCHAR2)
RETURN VARCHAR2
IS
pnodelist xmldom.domnodelist;
pnode xmldom.domnode;
pelement xmldom.domelement;
e_xmltag_notexists EXCEPTION;
BEGIN
pnodelist := xmldom.getchildrenbytagname (i_elem, i_tagname);
pnode := xmldom.item (pnodelist, 0);

IF (xmldom.isnull (pnode))
THEN
RAISE e_xmltag_notexists;
END IF;

IF xmldom.haschildnodes (pnode)
THEN
IF (xmldom.getnodetype (pnode) = xmldom.element_node)
THEN
pnode := xmldom.getfirstchild (pnode);
RETURN (xmldom.getnodevalue (pnode));
END IF;
END IF;

RETURN NULL;
EXCEPTION
WHEN e_xmltag_notexists
THEN
RETURN NULL;
END f_verify_and_gettag_value;

PROCEDURE p_get_node_value (io_outval IN OUT VARCHAR2,
i_tagnode xmldom.domnode,
i_tagname VARCHAR2)
IS
tagval VARCHAR2 (30000);
e_xmltag_notexists EXCEPTION;
BEGIN
BEGIN
tagval :=
f_verify_and_gettag_value (xmldom.makeelement (i_tagnode),
i_tagname);
io_outval := tagval;
EXCEPTION
WHEN e_xmltag_notexists
THEN
io_outval := NULL;
-- mr8328 on 12/01/2016
-- do not set out val if already exists as tag itself not there
NULL;
END;
END p_get_node_value;
BEGIN
reqxml := REPLACE (reqxml, '&', CHR (ASCII ('&')) || 'amp;'); -- To handle &

v_mds_xml_parser := DBMS_XMLPARSER.newparser;
xmlparser.parseclob (v_mds_xml_parser, reqxml);
v_mds_xml_doc := DBMS_XMLPARSER.getdocument (v_mds_xml_parser);

/* Checking Parent Node is exists or Not and Raising Exception if not present*/
v_Release_node :=
xmldom.item (xmldom.getelementsbytagname (v_mds_xml_doc, 'Release'), 0);

IF (xmldom.isnull (v_Release_node))
THEN
RAISE node_not_exists;
END IF;

/*ShipToLocationRef */
v_ShipToLocationRef_list :=
xmldom.getchildrenbytagname (xmldom.makeelement (v_Release_node),
'ShipToLocationRef');
v_ShipToLocationRef_node := xmldom.item (v_ShipToLocationRef_list, 0);

/*LocationRef */
v_LocationRef_list :=
xmldom.getchildrenbytagname (
xmldom.makeelement (v_ShipToLocationRef_node),
'LocationRef');
v_LocationRef_node := xmldom.item (v_LocationRef_list, 0);

/*Location */
v_Location_list :=
xmldom.getchildrenbytagname (xmldom.makeelement (v_LocationRef_node),
'Location');
v_Location_node := xmldom.item (v_Location_list, 0);

p_get_node_value (v_TransactionCode, v_Location_node, 'TransactionCode');
DBMS_OUTPUT.PUT_LINE ('v_TransactionCode=' || v_TransactionCode);

/*LocationGid */
v_LocationGid_list :=
xmldom.getchildrenbytagname (xmldom.makeelement (v_LocationRef_node),
'LocationGid');
v_LocationGid_node := xmldom.item (v_LocationGid_list, 0);

/*Gid */
v_Gid_list :=
xmldom.getchildrenbytagname (xmldom.makeelement (v_LocationGid_node),
'Gid');
v_Gid_node := xmldom.item (v_Gid_list, 0);

p_get_node_value (v_DomainName, v_Gid_node, 'DomainName');
p_get_node_value (v_Xid, v_Gid_node, 'Xid');
DBMS_OUTPUT.PUT_LINE ('v_DomainName=' || v_DomainName);
DBMS_OUTPUT.PUT_LINE ('v_Xid=' || v_Xid);

p_get_node_value (v_LocationName, v_Location_node, 'TransactionCode');
DBMS_OUTPUT.PUT_LINE ('v_LocationName=' || v_LocationName);

/*Address*/
v_Address_list :=
xmldom.getchildrenbytagname (xmldom.makeelement (v_Location_node),
'Address');
v_Address_node := xmldom.item (v_Address_list, 0);
p_get_node_value (v_AddressLine1, v_Address_node, 'AddressLine1');
p_get_node_value (v_AddressLine2, v_Address_node, 'AddressLine2');
p_get_node_value (v_city, v_Address_node, 'City');
p_get_node_value (v_Province, v_Address_node, 'Province');
p_get_node_value (v_PostalCode, v_Address_node, 'PostalCode');

DBMS_OUTPUT.PUT_LINE ('v_AddressLine1=' || v_AddressLine1);
DBMS_OUTPUT.PUT_LINE ('v_AddressLine2=' || v_AddressLine2);
DBMS_OUTPUT.PUT_LINE ('v_city=' || v_city);
DBMS_OUTPUT.PUT_LINE ('v_Province=' || v_Province);
DBMS_OUTPUT.PUT_LINE ('v_PostalCode=' || v_PostalCode);

/*CountryCode*/
v_CountryCode_list :=
xmldom.getchildrenbytagname (xmldom.makeelement (v_Address_node),
'Address');
v_CountryCode_node := xmldom.item (v_Address_list, 0);

p_get_node_value (v_CountryCode, v_CountryCode_node, 'CountryCode');
DBMS_OUTPUT.PUT_LINE ('v_CountryCode=' || v_CountryCode);
EXCEPTION
WHEN node_not_exists
THEN
DBMS_OUTPUT.put_line ('No node exists with the request XML');
WHEN OTHERS
THEN
DBMS_OUTPUT.put_line (
'Oracle Error Number:'
|| SQLCODE
|| 'Oracle Error Message:'
|| SQLERRM);
END;
/

v_TransactionCode=NP

v_DomainName=

v_Xid=

v_LocationName=NP

v_AddressLine1=189, GASAN DIGITAL 1-RO

v_AddressLine2=

v_city=GEUMCHEON-GU

v_Province=

v_PostalCode=08503

v_CountryCode=

PL/SQL procedure successfully completed.

SQL>

Oracle SQL & PL/SQL

Tuesday, November 19, 2019

How to Parse XML using DOM Parser in Oracle

No comments:

Post a Comment