This discussion is archived
1 Reply Latest reply: Jul 2, 2012 6:45 AM by odie_63 RSS

XML in PLSQL without using SELECT statements

945447 Newbie
Currently Being Moderated
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 Guru
    Currently Being Moderated
    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.

Legend

  • Correct Answers - 10 points
  • Helpful Answers - 5 points