5 Replies Latest reply: Jun 26, 2012 1:55 PM by Jason_(A_Non) RSS

    XML parsing

    945447
      There are a lot of examples with ExistsNode (or better XMLexists) but they do not cover what I need now. If we start with this xmltype

      <?xml version="1.0" encoding="UTF-8"?>
      <ns0:GetRequest xmlns:ns0="http://www.rona.ca/ODS/simplified_namespace">
           <ns0:ProductIDs>
                <ns0:ID type="RDSKU">298222-0</ns0:ID>
           </ns0:ProductIDs>
           <ns0:ProductUom Usage="PURCU">
                <ns0:Quantity uomCode="EA">1</ns0:Quantity>
           </ns0:ProductUom>
      </ns0:GetRequest>

      How can we parse data inside using the member function ExistsNode? Basically I want to parse all values filtered and get the right data without using SELECT statements.

      I started with this good example XML

      set serveroutput on size 1000000
      DECLARE
      l_doc VARCHAR2(2000);
      l_domdoc dbms_xmldom.DOMDocument;
      l_nodelist dbms_xmldom.DOMNodeList;
      l_node dbms_xmldom.DOMNode;
      l_value VARCHAR2(30);

      l_xmltype XMLTYPE;
      l_empx XMLTYPE;
      l_index PLS_INTEGER;
      l_col_ind PLS_INTEGER;
      BEGIN
      l_doc := '<employees>
      <emp>
      <name>Scott</name>
      <favorites>
      <color>red</color>
      <color>orange</color>
      </favorites>
      </emp>
      <emp>
      <name>John</name>
      <favorites>
      <color>blue</color>
      <color>green</color>
      </favorites>
      </emp>
      </employees>';

      l_xmltype := XMLTYPE(l_doc);

      -- Method 3
      dbms_output.new_line;
      dbms_output.put_line('Method 3');
      l_index := 1;
      WHILE l_xmltype.Existsnode('/employees/emp[' || To_Char(l_index) || ']') > 0
      LOOP
      l_value := l_xmltype.extract('/employees/emp[' || To_Char(l_index) || ']/name/text()').getStringVal();
      dbms_output.put_line('Emp Name: '||l_value);
      l_index := l_index + 1;
      END LOOP;

      -- Method 5
      dbms_output.new_line;
      dbms_output.put_line('Method 5');
      l_index := 1;
      WHILE l_xmltype.Existsnode('/employees/emp[' || To_Char(l_index) || ']') > 0
      LOOP
      l_empx := l_xmltype.extract('/employees/emp[' || To_Char(l_index) || ']');
      l_col_ind := 1;
      WHILE l_empx.Existsnode('/emp/favorites/color[' || To_Char(l_col_ind) || ']') > 0
      LOOP
      l_value := l_empx.extract('/emp/favorites/color[' || To_Char(l_col_ind) || ']/text()').getStringVal();
      dbms_output.put_line('Color: '||l_value);
      l_col_ind := l_col_ind + 1;
      END LOOP;
      l_index := l_index + 1;
      END LOOP;
      END;

      The moment I add the ns0: prefix or namespace prefix it fails because I dont master the code anymore. So how should I use the namespace and namespace prefix to handle this example with the same logic and select the 4 values:

      <?xml version="1.0" encoding="UTF-8"?>
      <ns0:GetRequest xmlns:ns0="http://www.rona.ca/ODS/simplified_namespace">
           <ns0:ProductIDs>
                <ns0:ID type="RDSKU">298222-0</ns0:ID>
           </ns0:ProductIDs>
           <ns0:ProductUom Usage="PURCU">
                <ns0:Quantity uomCode="EA">1</ns0:Quantity>
           </ns0:ProductUom>
      </ns0:GetRequest>

      Example
      set serveroutput on size 1000000
      DECLARE
      l_doc VARCHAR2(2000);
      l_domdoc dbms_xmldom.DOMDocument;
      l_nodelist dbms_xmldom.DOMNodeList;
      l_node dbms_xmldom.DOMNode;
      l_value VARCHAR2(30);
      l_xmltype XMLTYPE;
      l_empx XMLTYPE;
      l_index PLS_INTEGER;
      l_col_ind PLS_INTEGER;
      v_namespace_prfx varchar2(20):= 'ns0:';
      v_namespace varchar2(200):= 'xmlns:ns0="http://www.rona.ca/ODS/simplified_namespace';
      BEGIN
      l_doc := '<?xml version="1.0" encoding="UTF-8"?>
      <ns0:GetRequest xmlns:ns0="http://www.rona.ca/ODS/simplified_namespace">
           <ns0:ProductIDs>
                <ns0:ID type="RDSKU">298222-0</ns0:ID>
           </ns0:ProductIDs>
           <ns0:ProductUom Usage="PURCU">
                <ns0:Quantity uomCode="EA">1</ns0:Quantity>
           </ns0:ProductUom>
      </ns0:GetRequest>';
      l_xmltype := XMLTYPE(l_doc);
      -- Method 3
      dbms_output.new_line;
      dbms_output.put_line('Method 3');
      l_index := 1;
      WHILE l_xmltype.Existsnode('/'||V_NAMESPACE_PRFX||'GetRequest/'||V_NAMESPACE_PRFX || 'ProductIDs/'||V_NAMESPACE_PRFX||'ID/@type[' || To_Char(l_index) || ']',v_namespace) > 0
      LOOP
      dbms_output.put_line('I am here');
      --l_value := l_xmltype.extract('/employees/emp[' || To_Char(l_index) || ']/name/text()').getStringVal();
      l_value := l_xmltype.Extract('/'||V_NAMESPACE_PRFX||'GetRequest/'||V_NAMESPACE_PRFX || 'ProductIDs/'||V_NAMESPACE_PRFX||'ID/@type[' || To_Char(l_index) || ']/text(),v_namespace').getStringVal();
      dbms_output.put_line('Emp Name: '||l_value);
      l_index := l_index + 1;
      END LOOP;

      END;

      gives me the error at Extract line:
      Error report:
      ORA-31011: XML parsing failed
      ORA-19202: Error occurred in XML processing
      LPX-00601: Invalid token in: '/ns0:GetRequest/ns0:ProductIDs/ns0:ID/@type[1]/text(),v_namespace'
      ORA-06512: at "SYS.XMLTYPE", line 99
      ORA-06512: at line 32
      31011. 00000 - "XML parsing failed"
      *Cause: XML parser returned an error while trying to parse the document.
      *Action: Check if the document to be parsed is valid.

      Method 3
      I am here




      Thanks very much for your input

      Edited by: 942444 on Jun 24, 2012 9:26 AM
        • 1. Re: XML parsing
          odie_63
          How can we parse data inside using the member function ExistsNode? Basically I want to parse all values filtered and get the right data without using SELECT statements.
          You don't need existsNode() function to parse the sample XML you gave.

          Any good reason for not using XMLTable (or XMLSequence in old versions)?

          To answer your main question, the (now deprecated) XMLType functions and methods have a parameter we can use to declare namespace mappings.
          For example :
          SQL> DECLARE
            2  
            3    l_doc VARCHAR2(2000);
            4    l_xmltype XMLTYPE;
            5    l_nsmap VARCHAR2(80) := 'xmlns="http://www.rona.ca/ODS/simplified_namespace"';
            6  
            7  BEGIN
            8  
            9    l_doc := '<?xml version="1.0" encoding="UTF-8"?>
           10  <ns0:GetRequest xmlns:ns0="http://www.rona.ca/ODS/simplified_namespace">
           11  <ns0:ProductIDs>
           12  <ns0:ID type="RDSKU">298222-0</ns0:ID>
           13  </ns0:ProductIDs>
           14  <ns0:ProductUom Usage="PURCU">
           15  <ns0:Quantity uomCode="EA">1</ns0:Quantity>
           16  </ns0:ProductUom>
           17  </ns0:GetRequest>';
           18  
           19    l_xmltype := XMLTYPE(l_doc);
           20  
           21    dbms_output.put_line('ID type          = ' || l_xmltype.extract('/GetRequest/ProductIDs/ID/@type', l_nsmap).getStringVal());
           22    dbms_output.put_line('ID               = ' || l_xmltype.extract('/GetRequest/ProductIDs/ID/text()', l_nsmap).getStringVal());
           23    dbms_output.put_line('ProductUom Usage = ' || l_xmltype.extract('/GetRequest/ProductUom/@Usage', l_nsmap).getStringVal());
           24    dbms_output.put_line('Quantity uomCode = ' || l_xmltype.extract('/GetRequest/ProductUom/Quantity/@uomCode', l_nsmap).getStringVal());
           25    dbms_output.put_line('Quantity         = ' || l_xmltype.extract('/GetRequest/ProductUom/Quantity/text()', l_nsmap).getStringVal());
           26  
           27  END;
           28  /
           
          ID type          = RDSKU
          ID               = 298222-0
          ProductUom Usage = PURCU
          Quantity uomCode = EA
          Quantity         = 1
           
          PL/SQL procedure successfully completed
           
          • 2. Re: XML parsing
            945447
            Great example, thanks. The reason why I need ExistsNode, is because I can have more than 1 value. XML values should be found with iterations. This example...


            l_doc := '<?xml version="1.0" encoding="UTF-8"?>
            <ns0:GetRequest xmlns:ns0="http://www.rona.ca/ODS/simplified_namespace">
                 <ns0:ProductIDs>
                 <ns0:ID type="RDSKU">298222-0</ns0:ID>
            <ns0:ID type="WARE">298222-1</ns0:ID>
                 </ns0:ProductIDs>
                 <ns0:ProductUom Usage="PURCU">
                      <ns0:Quantity uomCode="EA">1</ns0:Quantity>
                 </ns0:ProductUom>
            </ns0:GetRequest>';

            So the logic is, if you find the value, you extract and fill the next one, so on.

            I am in rooky in xml, so would you consider an example using XMLExists and XTRACT. I do want to avoid SELECT inside PLSQL code for better performance.
            Can you give me an example here starting with my code, which generates 2 values RDSKU, WARE using the loop ?
                 <ns0:ID type="RDSKU">298222-0</ns0:ID>
            <ns0:ID type="WARE">298222-1</ns0:ID>

            I would add that i need the [1] give me RDSKU and number 2 WARE. Right now, if I add a line I get a concatenation.

            This example works fine, see method number 3

            DECLARE
            l_doc VARCHAR2(2000);
            l_domdoc dbms_xmldom.DOMDocument;
            l_nodelist dbms_xmldom.DOMNodeList;
            l_node dbms_xmldom.DOMNode;
            l_value VARCHAR2(30);

            l_xmltype XMLTYPE;
            l_empx XMLTYPE;
            l_index PLS_INTEGER;
            l_col_ind PLS_INTEGER;
            BEGIN
            l_doc := '<employees>
            <emp>
            <name>Scott</name>
            <favorites>
            <color>red</color>
            <color>orange</color>
            </favorites>
            </emp>
            <emp>
            <name>John</name>
            <favorites>
            <color>blue</color>
            <color>green</color>
            </favorites>
            </emp>
            </employees>';

            l_domdoc := dbms_xmldom.newDomDocument(l_doc);

            -- Method 1
            dbms_output.put_line('Method 1');
            l_nodelist := dbms_xslprocessor.selectNodes(dbms_xmldom.makeNode(l_domdoc),'/employees/emp/name');
            FOR cur_emp IN 0 .. dbms_xmldom.getLength(l_nodelist) - 1 LOOP
            l_node := dbms_xmldom.item(l_nodelist, cur_emp);
            l_value := dbms_xmldom.getnodevalue(dbms_xmldom.getfirstchild(l_node));
            dbms_output.put_line('Emp Name: '||l_value);
            END LOOP;

            -- Method 2
            dbms_output.new_line;
            dbms_output.put_line('Method 2');
            l_nodelist := dbms_xmldom.getelementsbytagname(l_domdoc, 'name');
            -- get first item from list, could loop as shown above
            l_node := dbms_xmldom.item(l_nodelist, 0);
            l_value := dbms_xmldom.getnodevalue(dbms_xmldom.getfirstchild(l_node));
            dbms_output.put_line('Emp Name: '||l_value);

            -- Done with DOMDocument examples, setup for XMLType based examples
            dbms_xmldom.freeDocument(l_domdoc);
            l_xmltype := XMLTYPE(l_doc);

            -- Method 3
            dbms_output.new_line;
            dbms_output.put_line('Method 3');
            l_index := 1;
            WHILE l_xmltype.Existsnode('/employees/emp[' || To_Char(l_index) || ']') > 0
            LOOP
            l_value := l_xmltype.extract('/employees/emp[' || To_Char(l_index) || ']/name/text()').getStringVal();
            dbms_output.put_line('Emp Name: '||l_value);
            l_index := l_index + 1;
            END LOOP;

            -- Method 4
            dbms_output.new_line;
            dbms_output.put_line('Method 4');
            l_value := l_xmltype.extract('/employees/emp[2]/name/text()').getStringVal();
            dbms_output.put_line('Emp Name: '||l_value);

            -- Method 5
            dbms_output.new_line;
            dbms_output.put_line('Method 5');
            l_index := 1;
            WHILE l_xmltype.Existsnode('/employees/emp[' || To_Char(l_index) || ']') > 0
            LOOP
            l_empx := l_xmltype.extract('/employees/emp[' || To_Char(l_index) || ']');
            l_col_ind := 1;
            WHILE l_empx.Existsnode('/emp/favorites/color[' || To_Char(l_col_ind) || ']') > 0
            LOOP
            l_value := l_empx.extract('/emp/favorites/color[' || To_Char(l_col_ind) || ']/text()').getStringVal();
            dbms_output.put_line('Color: '||l_value);
            l_col_ind := l_col_ind + 1;
            END LOOP;
            l_index := l_index + 1;
            END LOOP;
            END;

            Edited by: 942444 on Jun 24, 2012 10:02 AM
            • 3. Re: XML parsing
              odie_63
              What's the database version btw?

              Example with existsNode and extract :
              SQL> DECLARE
                2  
                3    l_doc VARCHAR2(2000);
                4    l_xmltype XMLTYPE;
                5    l_nsmap VARCHAR2(80) := 'xmlns="http://www.rona.ca/ODS/simplified_namespace"';
                6    l_index PLS_INTEGER;
                7    l_value VARCHAR2(30);
                8  
                9  BEGIN
               10  
               11    l_doc := '<?xml version="1.0" encoding="UTF-8"?>
               12  <ns0:GetRequest xmlns:ns0="http://www.rona.ca/ODS/simplified_namespace">
               13  <ns0:ProductIDs>
               14  <ns0:ID type="RDSKU">298222-0</ns0:ID>
               15  <ns0:ID type="WARE">298222-1</ns0:ID>
               16  </ns0:ProductIDs>
               17  <ns0:ProductUom Usage="PURCU">
               18  <ns0:Quantity uomCode="EA">1</ns0:Quantity>
               19  </ns0:ProductUom>
               20  </ns0:GetRequest>';
               21  
               22    l_xmltype := XMLTYPE(l_doc);
               23    l_index := 1;
               24  
               25    WHILE l_xmltype.existsNode('/GetRequest/ProductIDs/ID['||to_char(l_index)||']', l_nsmap) = 1
               26      LOOP
               27        l_value := l_xmltype.extract('/GetRequest/ProductIDs/ID['||to_char(l_index)||']/@type', l_nsmap).getStringVal();
               28        dbms_output.put_line('ID type = ' || l_value);
               29        l_index := l_index + 1;
               30      END LOOP;
               31  
               32  END;
               33  /
               
              ID type = RDSKU
              ID type = WARE
               
              PL/SQL procedure successfully completed
               
              XMLExists() is an SQL function, so you can't use it in pure PL/SQL.
              I do want to avoid SELECT inside PLSQL code for better performance.
              Not much of a good reason if you want my opinion.
              PL/SQL is designed to work closely with SQL, and embedded SQLs in PL/SQL code don't have as much impact as the other way around.

              For small XML documents, using SQL and XMLTable with a transient (in-memory) XMLType instance will give you the same performance, and is simpler to use :
              SQL> DECLARE
                2  
                3    l_doc VARCHAR2(2000);
                4  
                5  BEGIN
                6  
                7    l_doc := '<?xml version="1.0" encoding="UTF-8"?>
                8  <ns0:GetRequest xmlns:ns0="http://www.rona.ca/ODS/simplified_namespace">
                9  <ns0:ProductIDs>
               10  <ns0:ID type="RDSKU">298222-0</ns0:ID>
               11  <ns0:ID type="WARE">298222-1</ns0:ID>
               12  </ns0:ProductIDs>
               13  <ns0:ProductUom Usage="PURCU">
               14  <ns0:Quantity uomCode="EA">1</ns0:Quantity>
               15  </ns0:ProductUom>
               16  </ns0:GetRequest>';
               17  
               18    FOR r IN (
               19      SELECT type, id
               20      FROM XMLTable(
               21             XMLNamespaces(default 'http://www.rona.ca/ODS/simplified_namespace')
               22           , '/GetRequest/ProductIDs/ID'
               23             passing xmltype(l_doc)
               24             columns type varchar2(15) path '@type'
               25                   , id   varchar2(15) path 'text()'
               26           )
               27    )
               28    LOOP
               29      dbms_output.put_line('ID = ' || r.id || ', type = ' || r.type);
               30    END LOOP;
               31  
               32  END;
               33  /
               
              ID = 298222-0, type = RDSKU
              ID = 298222-1, type = WARE
               
              PL/SQL procedure successfully completed
               
              If you're planning to deal with large XML documents, consider storing them in an XMLType table (binary storage if available) and use XMLTable to query it.
              • 4. Re: XML parsing
                945447
                Splendid, thanks, one last question. I am trying to deduct the values 298222-0 and 298222-1 from the same loop and I fail. What is the syntax which could help me.


                set serveroutput on size 100000

                DECLARE
                l_doc VARCHAR2(2000);
                l_xmltype XMLTYPE;
                l_nsmap VARCHAR2(80) := 'xmlns="http://www.rona.ca/ODS/simplified_namespace"';
                l_index PLS_INTEGER;
                l_value VARCHAR2(30);
                l_value1 VARCHAR2(100);

                BEGIN

                l_doc := '<?xml version="1.0" encoding="UTF-8"?>
                <ns0:GetRequest xmlns:ns0="http://www.rona.ca/ODS/simplified_namespace">
                <ns0:ProductIDs>
                <ns0:ID type="RDSKU">298222-0</ns0:ID>
                <ns0:ID type="WARE">298222-1</ns0:ID>
                </ns0:ProductIDs>
                <ns0:ProductUom Usage="PURCU">
                <ns0:Quantity uomCode="EA">1</ns0:Quantity>
                </ns0:ProductUom>
                </ns0:GetRequest>';

                l_xmltype := XMLTYPE(l_doc);
                l_index := 1;

                WHILE l_xmltype.existsNode('/GetRequest/ProductIDs/ID['||to_char(l_index)||']', l_nsmap) = 1
                LOOP
                l_value := l_xmltype.extract('/GetRequest/ProductIDs/ID['||to_char(l_index)||']/@type', l_nsmap).getStringVal();
                l_value1 := l_xmltype.extract('/GetRequest/ProductIDs/ID['||to_char(l_index)||']', l_nsmap).getStringVal();
                dbms_output.put_line('ID type = ' || l_value);
                dbms_output.put_line('ID type = ' || l_value1);
                l_index := l_index + 1;
                END LOOP;
                END;
                /

                Edited by: 942444 on 2012-06-26 11:01
                • 5. Re: XML parsing
                  Jason_(A_Non)
                  You are looking to replace the line with
                        l_value1 := l_xmltype.extract('/GetRequest/ProductIDs/ID['||to_char(l_index)||']/text()', l_nsmap).getStringVal();
                  The only difference is that I added /text() to bring back the contents of the node and not the node itself.