How to parse Oracle CLOB XML data to get node value?

Question

I am trying to parse a huge XML CLOB data to get some node values. So I wrote a method with the help of DBMS_XMLPARSER to retrieve a value of a node and it works fine.

FUNCTION Get_Node_Value(
      node_ IN DBMS_XMLDOM.DOMNode) RETURN VARCHAR2
   IS
      sub_nodes_list_  DBMS_XMLDOM.DOMNodeList;
      sub_nodes_len_   NUMBER;
      value_node_      DBMS_XMLDOM.DOMNode;
      value_           VARCHAR2(50);
   BEGIN
      sub_nodes_list_ := DBMS_XMLDOM.Getchildnodes(node_); 
      sub_nodes_len_  := DBMS_XMLDOM.GetLength(sub_nodes_list_); -- sub_nodes_len_ should be 1
      IF ( sub_nodes_len_ > 0 ) THEN
            value_node_ := DBMS_XMLDOM.Item(sub_nodes_list_, 0);
            value_      := DBMS_XMLDOM.GetNodeValue(value_node_);
      END IF;      
      RETURN value_;
   END Get_Node_Value;

But I am wondering to have a common method where I give XML CLOB and node name as parameters and get node value return back or something relevant. Since the XML is too large, using such a method will cut down unnecessary coding. Any help will be appreciated!

Arkadiusz Łukasiewicz · Accepted Answer · 2016-06-15 05:26:54Z

1

Xquery is a query and "programing" language designed to work with xml document. Oracle has two main function which accept xquery commands. xmltable and xmlquery. usign xmltable and xmlquery

Short example.

select * from xmltable('/ROWSET/ROW/TABLE_NAME/text()' passing xmltype('<?xml version="1.0"?>
                                    <ROWSET>
                                     <ROW>
                                      <TABLE_NAME>DBA_2PC_NEIGHBORS</TABLE_NAME>
                                      <COMMENTS>information about incoming and outgoing connections for pending transactions</COMMENTS>
                                     </ROW>
                                     <ROW>
                                      <TABLE_NAME>DBA_2PC_PENDING</TABLE_NAME>
                                      <COMMENTS>info about distributed transactions awaiting recovery</COMMENTS>
                                     </ROW>
                                     <ROW>
                                      <TABLE_NAME>DBA_ADDM_FDG_BREAKDOWN</TABLE_NAME>
                                     </ROW>
                                    </ROWSET>') )

answered Jun 15, 2016 at 5:26

Arkadiusz Łukasiewicz

6,3761 gold badge13 silver badges17 bronze badges

Sign up to request clarification or add additional context in comments.

4 Comments

S.Roshanth Over a year ago

Thanks. Since I have XML message is in BLOB it seems difficult to use Xquery in PL/SQL code! errors are like "XML parse failure" any suggestions?

Arkadiusz Łukasiewicz Over a year ago

How are you trying to convert clob into xmltype?

S.Roshanth Over a year ago

Actually XML it is BLOB so I convert it to CLOB using DBMS_LOB.converttoclob method. Then using DBMS_XMLPARSER.parseclob() and DBMS_XMLPARSER.getDocument() methods to create DBMS_XMLDOM.DOMDocument. After that only I am using DBMS_XMLDOM package to parse the XML and retrieving the node value.

Arkadiusz Łukasiewicz Over a year ago

Try xmltype. One version of constructor accepts blob, and encoding chareset_id. charset id for NLS_CHARSET_ID('AL32UTF8')`. For more charesets check link

Collectives™ on Stack Overflow

How to parse Oracle CLOB XML data to get node value?

1 Answer 1

4 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

4 Comments

Your Answer

Sign up or log in

Post as a guest

Related