Wednesday, May 18, 2011

PL/SQL - REST Call and XML Parsing

Looking for an example to make a REST API call in PL/SQL and parse the xml response from the call? Here is one way to do it.
DECLARE
 req     utl_http.req;
 resp    utl_http.resp;
 line    VARCHAR2(1024);
 url     VARCHAR2(1024);
 content VARCHAR2(1024);

 p       xmlparser.Parser;
 doc     xmldom.DOMDocument;
 element    xmldom.DOMElement;
 nodeList xmldom.DOMNodeList;
 node     xmldom.DOMNode;
BEGIN
 dbms_output.enable(1000000);

 url := utl_url.escape('http://host:port/app/rest/something');

 -- example xml response:
 --<?xml version="1.0" encoding="UTF-8"?><something><name>Joe</name><age>40</age></something>

 req := utl_http.begin_request(url);
 utl_http.set_header(req, 'User-Agent', 'Mozilla/4.0');
 resp := utl_http.get_response(req);
 content := '';
 BEGIN
     LOOP
       utl_http.read_line(resp, line, TRUE);
       content := content || line;
     END LOOP;
     utl_http.end_response(resp);
     EXCEPTION
         WHEN utl_http.end_of_body THEN
           utl_http.end_response(resp);
 END;

 dbms_output.put_line(content);

 p := xmlparser.newParser;
 xmlparser.setValidationMode(p, FALSE);
 xmlparser.parseBuffer(p, content);
 doc  := xmlparser.getDocument(p);

 element := xmldom.getDocumentElement(doc);

 nodeList := xmldom.getElementsByTagName(element, 'name');
 node := xmldom.item(nodeList, 0);
 node := xmldom.getFirstChild(node);
 dbms_output.put_line('name: ' || xmldom.getNodeValue(node));

 nodeList := xmldom.getElementsByTagName(element, 'age');
 node := xmldom.item(nodeList, 0);
 node := xmldom.getFirstChild(node);
 dbms_output.put_line('age: ' || xmldom.getNodeValue(node));
END;

No comments: