Validate & Get Values from XML using PL/SQL

There are various methods available in PLSQL to read, write and validate XML. Following sample code has simple approach to read, validate and get element value from XML using DBMS_XMLDOM API. This sample will read employee name and print it in the console.

DECLARE
lc_return_msg VARCHAR2(1000);

lc_xml_buf VARCHAR2(2000);
lc_xml_value VARCHAR2(2000);
lc_resp_xml XMLType;
lc_xml_doc DBMS_XMLDOM.DOMDocument;

lc_xml_ndoc DBMS_XMLDOM.DOMNode;
lc_xml_docelem DBMS_XMLDOM.DOMElement;
lc_xml_node DBMS_XMLDOM.DOMNode;
lc_xml_childnode DBMS_XMLDOM.DOMNode;
lc_xml_nodelist DBMS_XMLDOM.DOMNodelist;
BEGIN
-- XML Message
lc_return_msg := '<?xml version="1.0" encoding="utf-8"?>
<Department>
<Code>001</Code>
<Name>Sales</Name>
<Employees>
<Employee>
<FirstName>Scott</FirstName>
<LastName>Tiger</LastName>
<DoB>01/01/1975</DoB>
</Employee>
<Employee>
<FirstName>Adam</FirstName>
<LastName>Ford</LastName>
<DoB>12/03/1983</DoB>
</Employee>
</Employees>
</Department>';


-- Validate XML
lc_resp_xml := XMLType(lc_return_msg);

-- Create DOMDocument Handle
lc_xml_doc := DBMS_XMLDOM.newDOMDocument(lc_resp_xml);
lc_xml_ndoc := DBMS_XMLDOM.makeNode(lc_xml_doc);

DBMS_XMLDOM.writeToBuffer(lc_xml_ndoc, lc_xml_buf);

-- Get all elements
lc_xml_docelem := DBMS_XMLDOM.getDocumentElement(lc_xml_doc);

-- Get Result Elemement
lc_xml_nodelist := DBMS_XMLDOM.getElementsByTagName(lc_xml_docelem, 'FirstName');

-- Get Second Employee Name
lc_xml_node := DBMS_XMLDOM.item(lc_xml_nodelist, 1);
lc_xml_childnode := DBMS_XMLDOM.getFirstChild(lc_xml_node);
lc_xml_value := DBMS_XMLDOM.getNodeValue(lc_xml_childnode);

-- Print second employee name
DBMS_OUTPUT.put_line(lc_xml_value);
END;


No comments: