Extracting XML values In Oracle/PL SQL - Explained

XML values Extraction From Oracle/ PL SQL

How to Extract Values From XML In Oracle/PL SQL:
              Few ways available in oracle using which we can extract values from XML in PLSQL. Here we will be using couple of them among those, namely, 
  1. XMLTYPE
  2. XMLDOM

XMLTYPE is simply a data type released in Oracle 9i that has built in functions through which we can extract the XML values.

Functions:
EXTRACT helps to get the xml path
EXTRACT VALUE helps to get the value of the xml path provided to it as input.
XMLDOM is an oracle package which has many procedures and functions residing in it, with which we can extract values from XML.

DECLARE
   v_xml           CLOB
      := '<Main>
<Header>
<uname>Teentack</uname>
<Timestamp>14-10-2014 09:45:32</Timestamp>
<Title>XML Extraction</Title>
</Header>
<Lines>
<Line no="1">
<message>Line 1 message</message>
</Line>
<Line no="2">
<message>Line 2 message</message>
</Line>
<Line no="3">
<message>Line 3 message</message>
</Line>
</Lines>
</Main>';
   v_retdoc        xmldom.domdocument;
   v_parser        xmlparser.parser;
   v_err_flag      CHAR               := 'N';
   v_uname         VARCHAR2 (50);
   v_time          VARCHAR2 (50);
   v_title         VARCHAR2 (50);
   v_msg           VARCHAR2 (50);
   v_elmnt_nodes   xmldom.domnodelist;
   v_nodelist      xmldom.domnodelist;
   v_nodelist1     xmldom.domnodelist;
   v_root_node     xmldom.domelement;
   v_node_ele      xmldom.domelement;
   v_node_ele1     xmldom.domelement;
   v_node          xmldom.domnode;
   v_first_child   xmldom.domnode;
   v_node1         xmldom.domnode;
   v_node2         xmldom.domnode;
   v_len           NUMBER;
   v_len1          NUMBER;
   v_len2          NUMBER;
   nspace          VARCHAR2 (50);
BEGIN
--    Checking XML is well formed or not
   BEGIN
      v_parser := xmlparser.newparser;
      xmlparser.parseclob (v_parser, v_xml);

/*If XML is incorrect this will rech exception part
Parseclob is used in case of input XML is of clob type
Parse will be used if the xml file is taken from any URL
Parsebuffer will used if the xml is of varchar type*/

      v_retdoc := xmlparser.getdocument (v_parser);
      xmlparser.freeparser (v_parser);
      DBMS_OUTPUT.put_line('XML is well formed');
   EXCEPTION
      WHEN OTHERS
      THEN
         v_err_flag := 'Y';
         xmlparser.freeparser (v_parser);
         DBMS_OUTPUT.put_line(   'XML is not well formed and its has '
                               || SQLERRM
                               || ' error'
                              );
   END;

--    End Checking XML is well formed or not

--      Extracting values using XMLTYPE
   IF v_err_flag = 'N'
   THEN
      BEGIN
         SELECT EXTRACTVALUE (x.DATA, 'Header/uname'),
                EXTRACTVALUE (x.DATA, 'Header/Timestamp'),
                EXTRACTVALUE (x.DATA, 'Header/Title'),
                EXTRACTVALUE (x.DATA, 'Lines/Line[2]/message')
           INTO v_uname,
                v_time,
                v_title,
                v_msg
           FROM (SELECT EXTRACT ((XMLTYPE (v_xml)), '//Main/*') AS DATA
                   FROM DUAL) x;
/*Above Select Statement Explanation
In the from clause first the input xml will be converted to XMLTYPE, then the path //Main/* will be extracted using EXTRACT function and stored into the alias name DATA
Here // indicates the root
And * indicates to fetch all the xpaths under Main node

In the Select clause x.DATA is the xpath obtained in from clause which is given as input to the EXTRACTVALUE function and other input is the following path from which we want to extract the value

EXTRACTVALUE (x.DATA, 'Lines/Line[2]/message')
If you see the input xml, there are three line nodes inside lines node.
So in order to get the second line node message value we used [2].
So we cannot loop through the lines node to get each line node value using XMLTYPE. Hence we go for XMLDOM to loop through XML.
*/
         DBMS_OUTPUT.put_line('Username :' || v_uname);
         DBMS_OUTPUT.put_line('Timestamp :' || v_time);
         DBMS_OUTPUT.put_line('Title :' || v_title);
         DBMS_OUTPUT.put_line('Line Message from XMLTYPE:' || v_msg);
      EXCEPTION
         WHEN OTHERS
         THEN
            v_err_flag := 'Y';
            DBMS_OUTPUT.put_line
                              (   'Error while extracting XML using XMLTYPE '
                               || SQLERRM
                              );
      END;

--      END Extracting values using XMLTYPE
--      Extracting Line values using XMLDOM
      IF v_err_flag = 'N'
      THEN
         BEGIN
            v_root_node := xmldom.getdocumentelement (v_retdoc);
            nspace := xmldom.getnamespace (v_root_node);
            v_elmnt_nodes :=
                       xmldom.getchildrenbytagname (v_root_node, '*', nspace);
            v_len := xmldom.getlength (v_elmnt_nodes);
/*
xmldom.getdocumentelement     = gets the root node
xmldom.getchildrenbytagname   = returns the children nodelist of the root node
in this case it will return
Headers node &
Lines node
Which are the child nodes of the root node Main
Instead of * if we give child nodes name it will return that particular child node only
Something like xmldom.getchildrenbytagname (v_root_node, 'Lines', nspace);
xmldom.getlength returns the length of the nodelist
*/
            FOR i IN 0 .. v_len - 1
            LOOP
               v_node := xmldom.item (v_elmnt_nodes, i);

               IF xmldom.getnodename (v_node) = 'Lines'
               THEN
/*looping through the nodelist and checking for the Lines node
*/
                  v_node_ele := xmldom.makeelement (v_node);
                  v_nodelist := xmldom.getchildrenbytagname (v_node_ele, '*', nspace);
                  v_len1 := xmldom.getlength (v_nodelist);
/*Once again here we will get the nodelist under lines node and get its length
Now we are in lines node*/

                  FOR j IN 0 .. v_len1 - 1
                  LOOP
                     v_node1 := xmldom.item (v_nodelist, j);
                     v_node_ele1 := xmldom.makeelement (v_node1);
                     v_nodelist1 := xmldom.getelementsbytagname (v_node_ele1, '*',
                                                     nspace);
                     v_len2 := xmldom.getlength (v_nodelist1);

                     FOR k IN 0 .. v_len2 - 1
                     LOOP


/*now we are looping through the nodelist under lines node and checking for message node and printing its value
xmldom.getelementsbytagname = similar to xmldom. getchildrenbytagname  but returns all the nodes instead of just returning the child nodes

Lines node has 3 line node as its children but the getelementsbytagname not only returns the these 3 line node also returns the message node which is not the direct child of Lines node */
                        v_first_child := NULL;
                        v_node2 := xmldom.item (v_nodelist1, k);

                        IF xmldom.getnodename (v_node2) = 'message'
                        THEN
                           v_first_child := xmldom.getfirstchild (v_node2);

                           IF NOT xmldom.isnull (v_first_child)
                           THEN
                              DBMS_OUTPUT.put_line
                                          (   'message '
                                           || xmldom.getnodevalue
                                                                (v_first_child)
                                          );
                           END IF;
                        END IF;
                     END LOOP;
                  END LOOP;
               END IF;
            END LOOP;
         EXCEPTION
            WHEN OTHERS
            THEN
               DBMS_OUTPUT.put_line
                               (   'Error while extracting XML using XMLDOM '
                                || SQLERRM
                               );
         END;
      END IF;
--      End Extracting Line values using XMLDOM
   END IF;
EXCEPTION
   WHEN OTHERS
   THEN
      DBMS_OUTPUT.put_line(SQLERRM);
END;

Do comment below for queries on this topic and support or help needed.

Tags: XML, PLSQL, EXTRACT, EXTRACTVALUE, XMLTYPE, XMLDOM, XMLPARSER, XML FROM PLSQL.

3 comments :

  1. thank a lot, the information was very useful

    ReplyDelete
  2. I need help to traverse a xml. How to traverse a XML to find the immediate child tag? For example:

    3
    99999

    345678
    35464
    2015
    2
    0
    33671
    3


    4646



    75757

    04
    SORRY




    If I have to find out that under parent tag is there any immediate child is present or not?

    ReplyDelete

Related Posts Plugin for WordPress, Blogger...
 

Do not Copy this Web Page Content

Copyrighted.com Registered & Protected  0K43-TWEU-OGOE-SNU2 DMCA.com Protection Status
The content displayed on this website are copyright protected, reproducing the article in any kind is strictly prohibited.
Copyright © . How to Comp - Instant Solution for Computer and Mobile Problems | All Rights Reserved.
Designed By Way2themes | True Tech Tips | Teentack