Yet Another way of Extracting XML Data

We shall insert the xml into a table and retrieve that


CREATE TABLE xml_test
(
   id          NUMBER DEFAULT 1 ,
   xml_text    VARCHAR2 (4000 BYTE),
   ts_update   DATE DEFAULT SYSDATE
)

SET DEFINE OFF;Insert into XML_TEST
   (ID, XML_TEXT, TS_UPDATE)
 Values
   (2, '<page>
  <id>72054439</id>
  <redactions>
    <redaction>
      <id>0</id>
      <privileges>
        <privilege>
              <id>164</id>
              <Code>ACP</Code>
         </privilege>
               <privilege>       
                  <id>165</id>
                  <Code>AWPD</Code>
               </privilege>
      </privileges>
     </redaction>
     <redaction>
       <id>0</id>
        <privileges>
          <privilege>
                   <id>164</id>
                  <Code>ACP</Code>
            </privilege>
            <privilege>
                  <id>165</id>
                  <Code>AWPD</Code>
            </privilege>
      </privileges>
    </redaction>
  </redactions>
</page>', SYSDATE);
COMMIT;


This Query

SELECT   x1.id page_id, x2.id privilege_id, x2.code privilege_code
  FROM   xml_test t,
         XMLTABLE ('/page' 
                   PASSING xmltype (t.xml_text) 
                   COLUMNS 
                        id NUMBER PATH './id', 
                        xml_privilege XMLTYPE PATH '/page/redactions/redaction/privileges'
                   ) x1,
         XMLTABLE ('/privileges/privilege' 
                    PASSING x1.xml_privilege 
                    COLUMNS
                          id NUMBER PATH './id', 
                          code VARCHAR (5) PATH './Code'
                  ) x2
 WHERE   t.id = 2

would return  PAGE_ID            PRIVILEGE_ID      PRIVILEGE_CODE

72054439      164                         ACP
72054439      165                         AWPD
72054439      164                         ACP
72054439      165                         AWPD

Extract XML Data - ORACLE

How do you extract a tag from a XML


Using XML DATA

Below is a Sample

declare
       xml_data xmltype;
      destination_var varchar2(2000);
      destination_num number;
begin
     xml_data:=xmltype('<tag><char>Some text</char><num>123</num></tag>');
    destination_var := xml_data.extract('/tag/char/text()').getStringVal;
    destination_num := xml_data.extract('/tag/num/text()').getNumberVal;
    dbms_output.put_line('/tag/char ' ||destination_var);
    dbms_output.put_line('/tag/num ' ||destination_num);
end;

Would give the below Output
/tag/char Some text
/tag/num 123

Sample for Having more than one node in XML 

declare
       xml_data xmltype;
      destination_var varchar2(2000);
      destination_num number;
begin
     xml_data:=xmltype('<tag><char>Some text</char><num>123</num><char>Next text</char><num>987</num></tag>');
    destination_var := xml_data.extract('/tag/char/text()').getStringVal;
    destination_num := xml_data.extract('/tag/num/text()').getNumberVal;
    dbms_output.put_line('/tag/char ' ||destination_var);
    dbms_output.put_line('/tag/num ' ||destination_num);
end;

Would give the below output
/tag/char Some textNext text
/tag/num 123987