Obtaining the value of an XML element in a VARCHAR2


We’ve got some XML data stored in the comments against tables on a system I’m working on – things like “Table Short Name (alias)”, “Legacy Key” and “SCD Type”, e.g.


select table_name,comments
from dba_tab_comments
where table_name='BDM_T_CUSTOMER';

Table
Name COMMENTS
------------------------------ ---------------------
BDM_T_CUSTOMER CUS


We’re storing extra metadata in the comments for things we can’t easily store anywhere else.

Now how do we get the value of a specific element, e.g. alias ?

I figured this would be easy…but it seems that all the examples I come across are for extracting such elements when they are in an XMLType column not a VARCHAR2.

Looking in Chapter 5 of Building Oracle XML Applications by Steve Muench I found an example showing how to get the value of an XML attribute. Steve gave some sample code to get the attribute value and explained that getting the element value was also possible but a little more tricky…he explained the general method but unfortunately there was no example or code for that so I had to play around with Steve’ code for the attribute stuff until I came up with this for an element…


CREATE OR REPLACE PACKAGE xml_utils AS
FUNCTION parse_xml(p_xml VARCHAR2) RETURN xmldom.DOMDocument;
FUNCTION get_element_value(p_xmldoc VARCHAR2
,p_element VARCHAR2) RETURN VARCHAR2;
END xml_utils;
/

CREATE OR REPLACE PACKAGE BODY xml_utils AS

FUNCTION parse_xml(p_xml VARCHAR2) RETURN xmldom.DOMDocument IS
l_return_document xmldom.DOMDocument;
parser xmlparser.Parser;
e_parse_error EXCEPTION;
PRAGMA EXCEPTION_INIT(e_parse_error,-20100);
BEGIN
parser := xmlparser.newParser;
xmlparser.parseBuffer(parser,p_xml);
l_return_document := xmlparser.getDocument(parser);
xmlparser.freeParser(parser);
RETURN l_return_document;
EXCEPTION
WHEN e_parse_error THEN
xmlparser.freeParser(parser);
RETURN l_return_document;
END parse_xml;

FUNCTION get_element_value(p_xmldoc VARCHAR2
,p_element VARCHAR2) RETURN VARCHAR2 IS
xd_xmldoc xmldom.DOMDocument;
l_return_value VARCHAR2(4000);
l_node_list xmldom.DOMNodeList;
l_doc_node xmldom.DOMNode;
l_text_node xmldom.DOMNode;
BEGIN
IF LENGTH(LTRIM(RTRIM(p_xmldoc))) > 0 THEN
xd_xmldoc := parse_xml(p_xmldoc);
IF NOT xmldom.IsNull(xd_xmldoc) THEN
l_node_list := xmldom.getElementsByTagName(xd_xmldoc,p_element);
l_doc_node := xmldom.item(l_node_list, 0);
l_text_node := xmldom.getfirstchild(l_doc_node);
l_return_value := xmldom.getNodeValue(l_text_node);
xmldom.freeDocument(xd_xmldoc);
RETURN l_return_value;
ELSE
xmldom.freeDocument(xd_xmldoc);
RETURN NULL;
END IF;
ELSE
RETURN NULL;
END IF;
EXCEPTION
WHEN OTHERS THEN
RAISE_APPLICATION_ERROR(-20001,'SQL Error occured:'||SQLERRM);
END get_element_value;

END xml_utils;
/


Now testing for the above XML fragment…


select table_name
, xml_utils.get_element_value(comments,'ALIAS') as table_alias
from all_tab_comments
where table_name='BDM_T_CUSTOMER'
/

Table
Name TABLE_ALIAS
------------------------------ ------------
BDM_T_CUSTOMER CUS

Seems to work reasonably well although I’d be the first to admit I’m an XML novice so I’m sure there are ways to improve it.