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;
/
It becomes extremely necessary to cure such problems at an early stage when the woman  lowest viagra price becomes pregnant. Erectile dysfunction is a harmful disorder which affects the love life of the person consuming it. greyandgrey.com levitra 60 mg Any mechanic can  cheapest price for tadalafil fix these accessories to your motorcycle. Sometimes impotence due to a physical condition may be linked to the condition like deterioration to the blood vessels, which may be fixed through surgery.  pfizer viagra 100mg />
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.