PLSQL parse XML file using DBMS_XMLDOM

Question

I have a simple XML file like so:

<?xml version="1.0" encoding="UTF-8"?>
<!--Generic Params-->
<PARAM_LIST>
    <PARAM>         
            <KEY>USERNAME</KEY>
            <VALUE>DAVID</VALUE>                        
    </PARAM>
</PARAM_LIST>

I would like to parse it using DBMS_XMLDOM, I enter the LOOP but I cannot get the value from nor KEY tag or VALUE, also, I'm not even sure that I fully access the PARAM tag.

    lv_ParamList := dbms_xmldom.getElementsByTagName(lv_res_domDoc, 'PARAM_LIST');

    IF NOT dbms_xmldom.isnull(lv_ParamList) THEN

       -- Looping over parameters
       FOR lv_ParamNo IN 0..dbms_xmldom.getLength(lv_ParamList)-1 LOOP

          lv_ParamNode := dbms_xmldom.item(lv_ParamList,lv_ParamNo);
          lv_TempNode := dbms_xmldom.getNamedItem(lv_ParamAttributes,'USERNAME');
          lv_Vc2_TagValue := dbms_xmldom.getValue(dbms_xmldom.makeattr(lv_TempNode));
          lv_TempNode := dbms_xmldom.getNamedItem(lv_ParamAttributes,'KEY');
          lv_Vc2_TagValue := dbms_xmldom.getValue(dbms_xmldom.makeattr(lv_TempNode));

       END LOOP;
  END IF;

Please assist.

Do yo have to use DBMS_XMLDOM, because there are more efficient built-in XML function in oracle? — OldProgrammer
– OldProgrammer, Commented Jun 20, 2016 at 11:49

David Faizulaev · Accepted Answer · 2016-06-20 15:43:26Z

Managed to overcome it like so:

 FOR lv_ParamNo IN 0..dbms_xmldom.getLength(lv_ParamList)-1 LOOP

            lv_ParamNode := DBMS_XMLDOM.Item(lv_ParamList, lv_ParamNo);
            lv_ParamSet := Dbms_Xmldom.Getchildnodes(lv_ParamNode);
            lv_Num_EventId := dbms_xmldom.getLength(lv_ParamSet);

              FOR lv_ParamInNo IN 0..dbms_xmldom.getLength(lv_ParamSet)-1 LOOP
                  lv_ParamKey := Dbms_Xmldom.Item(lv_ParamSet,lv_ParamInNo);
                  lv_Vc2_DeviceName := Dbms_Xmldom.Getnodename(lv_ParamKey);
                  lv_TempNode := Dbms_Xmldom.Getfirstchild(lv_ParamKey);
                  lv_Vc2_KeyTagName := Dbms_Xmldom.Getnodevalue(lv_TempNode);
                  IF lv_Vc2_KeyTagName = 'USERNAME' THEN
                    lv_ParamKey := Dbms_Xmldom.Item(lv_ParamSet,lv_ParamInNo+1);
                    lv_Vc2_TagName := Dbms_Xmldom.Getnodename(lv_ParamKey);
                    lv_TempNode := Dbms_Xmldom.Getfirstchild(lv_ParamKey);
                    lv_Vc2_FulUserName := Dbms_Xmldom.Getnodevalue(lv_TempNode);
                  ELSIF lv_Vc2_KeyTagName = 'ADDRESS' THEN
                    lv_ParamKey := Dbms_Xmldom.Item(lv_ParamSet,lv_ParamInNo+1);
                    lv_Vc2_TagName := Dbms_Xmldom.Getnodename(lv_ParamKey);
                    lv_TempNode := Dbms_Xmldom.Getfirstchild(lv_ParamKey);
                    lv_Vc2_Address := Dbms_Xmldom.Getnodevalue(lv_TempNode);
                  END IF; 
              END LOOP;
         END LOOP;

This means that I iterate the XML for each PARAM node. For each such node, I iterate the KEY VALUE pair, I get the KEY and move (in an inner loop) to the relevant value. A small downside is that I iterate over KEY -> Inner loop gets VALUE, iterate VALUE -> move to next key.

Collectives™ on Stack Overflow

PLSQL parse XML file using DBMS_XMLDOM

1 Answer 1

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related