1 Reply Latest reply: Jul 2, 2012 8:45 AM by odie_63 RSS

    XML in PLSQL without using SELECT statements

    945447
      It maybe crazy but I cannot find a book or an example to differentiate between these two ones, if it is english or french based on XML below.

      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="http://www.rona.ca/SharedResources/SchemaDefinitions/Canonical"';
      l_nsmap varchar2(200);
      BEGIN
      l_doc := '<?xml version = "1.0" encoding = "UTF-8"?>          
      <ns0:Product type = "DEPAS" xmlns:ns0 = "http://www.rona.ca/SharedResources/SchemaDefinitions/Canonical">          
           <ns0:ProductIDs>     
                <ns0:ID type = "RONCD">48535003</ns0:ID>
           </ns0:ProductIDs>     
           <ns0:ProductUom Usage = "PURCU">     
                <ns0:Quantity uomCode = "EA">1</ns0:Quantity>
           </ns0:ProductUom>     
           <ns0:ProductUom Usage = "SALEU">     
                <ns0:Quantity uomCode = "EA">1</ns0:Quantity>
           </ns0:ProductUom>     
           <ns0:CreateDateTime>2003-02-16T00:00:00-04:00</ns0:CreateDateTime>     
           <ns0:Status>     
                <ns0:Code>ACTV</ns0:Code>
           </ns0:Status>     
           <ns0:Description languageCode = "fra" type = "REGID">DEPOT BOUTEILLE</ns0:Description>     
           <ns0:Description languageCode = "eng" type = "REGID">BOTTLE DEPOSIT</ns0:Description>          
           <ns0:Description languageCode = "fra" type = "LONGD">DEPOT BOUTEILLE D''EAU</ns0:Description>          
           <ns0:Description languageCode = "eng" type = "LONGD">DEPOSIT WATER BOTTLE</ns0:Description>          
           <ns0:Description languageCode = "fra" type = "REGI4">DEPOT BOUTEILLE</ns0:Description>          
           <ns0:Description languageCode = "eng" type = "REGI4">BOTTLE DEPOSIT</ns0:Description>          
           <ns0:Description languageCode = "fra" type = "CLEI4">DEPOT</ns0:Description>          
           <ns0:Description languageCode = "eng" type = "CLEI4">DEPOSIT</ns0:Description>          
           <ns0:Description languageCode = "eng" type = "LONG4">DEPOSIT WATER BOTTLE</ns0:Description>          
           <ns0:Description languageCode = "fra" type = "LONG4">DEPOT BOUTEILLE D''EAU</ns0:Description>          
      </ns0:Product>';
      l_xmltype := XMLTYPE(l_doc);
      -- Method 3
      dbms_output.new_line;
      dbms_output.put_line('Method 3');
      l_index := 1;
      l_nsmap:=v_namespace;
      --dbms_output.put_line('ID type = ' || l_xmltype.extract('/GetRequest/ProductIDs/ID[2]/@type', l_nsmap).getStringVal());
      WHILE l_xmltype.Existsnode('/Product/'||'Description[' || To_Char(l_index) || ']',v_namespace) > 0
      LOOP
      dbms_output.put_line('I am here');
      -- dbms_output.put_line('ID = ' || l_xmltype.extract('/GetRequest/ProductIDs/ID/text()', l_nsmap).getStringVal());
      dbms_output.put_line('ID type = ' || l_xmltype.extract('/Product/'||'Description[' || To_Char(l_index) || ']/@type', l_nsmap).getStringVal());
      dbms_output.put_line('ID type = ' || l_xmltype.extract('/Product/'||'Description[' || To_Char(l_index) || ']/text()', l_nsmap).getStringVal());
      -- is it english or french ? how can I capture the difference ? my example does not work
      dbms_output.put_line('ID type = ' || l_xmltype.extract('/Product/'||'Description[' || To_Char(l_index) || '][languageCode = "eng"]/text()', l_nsmap).getStringVal());
      --dbms_output.put_line('Emp Name: '||l_value);
      l_index := l_index + 1;
      END LOOP;

      END;
        • 1. Re: XML in PLSQL without using SELECT statements
          odie_63
          I think you already know this since you extract the "type" attribute too.
          As well, languageCode is an attribute so you must access it with the "@" notation (or the attribute:: axis).

          Now, it depends on what you want to do :

          1- extract only Descriptions in a given language ?
          2- extract all Descriptions along with their language ?

          For 1) the current iteration method is not appropriate and you can't just add the corresponding predicate in the existsNode test because there's no correlation between the node position and the languageCode.
          You have to count how many nodes of the given language there are and then loop from 1 to this count.
          Or, fall back to case 2) and test the language within the loop after having extracted it.

          For 2) add another extract call that targets the attribute :
          WHILE l_xmltype.Existsnode('/Product/'||'Description[' || To_Char(l_index) || ']',v_namespace) > 0
          LOOP
            dbms_output.put_line('type = ' || l_xmltype.extract('/Product/'||'Description[' || To_Char(l_index) || ']/@type', l_nsmap).getStringVal());
            dbms_output.put_line('languageCode = ' || l_xmltype.extract('/Product/'||'Description[' || To_Char(l_index) || ']/@languageCode', l_nsmap).getStringVal());
            dbms_output.put_line('Description = ' || l_xmltype.extract('/Product/'||'Description[' || To_Char(l_index) || ']/text()', l_nsmap).getStringVal());
            l_index := l_index + 1;
          END LOOP;
          XMLTable is so much simpler to use in any case.