Create Table With XMLType And Insert XML Data
CREATE TABLE dw_xml_tab OF XMLType;

INSERT INTO dw_xml_tab VALUES(
 XMLType('<SALES>
            <REGION>
             <DIV>NORTH</DIV> 
             <AMOUNT>15500</AMOUNT>
             <MONTH>2006/01</MONTH>
            </REGION>
            <REGION>
             <DIV>SOUTH</DIV> 
             <AMOUNT>14150</AMOUNT>
             <MONTH>2006/01</MONTH>
            </REGION>
            <REGION>
             <DIV>EAST</DIV> 
             <AMOUNT>17250</AMOUNT>
             <MONTH>2006/01</MONTH>
            </REGION>
            <REGION>
             <DIV>WEST</DIV> 
             <AMOUNT>12700</AMOUNT>
             <MONTH>2006/01</MONTH>
            </REGION>
         </SALES>'));
COMMIT;
Query XMLTYPE Table
SELECT
  sys_XMLAgg(value(xt), XMLFormat('SALES')) xml_val,
  CAST(sys_XMLAgg(value(xt), XMLFormat('SALES')) AS VARCHAR2(1000)) xml_out
FROM dw_xml_tab doc,
     table(XMLSequence(extract(value(doc), '/SALES/REGION'))) xt
WHERE extractValue(value(xt), '/REGION/AMOUNT') >= 17000;
Query XMLTYPE Table


Oracle registered trademark of Oracle Corporation.

Last Revised On: August 13, 2014

  7616