Oracle DB - Extract XML data using XMLTABLE

Question

I am using XMLTABLE Function in Oracle 10g database and want to extract values from XML into Oracle Table.

 SELECT * 
 FROM xmltable(
                xmlnamespaces ('http://www.cool.com/totem/1.1' AS  "n1"), '/n1:totem/n1:results' 
                PASSING xmltype.createxml(('<?xml version="1.0" encoding="UTF-8" standalone="yes"?>
                                            <totem xmlns="http://www.cool.com/totem/1.1">
                                                <results>
                                                    <valuationDate>2014-07-31</valuationDate>
                                                    <clientID>220</clientID>
                                                    <energy>
                                                        <underlier>
                                                            <name>CO2 CER</name>
                                                            <group>European Emissions</group>
                                                            <units>EUR / MT</units>
                                                            <pricingTime>LDN 17:00</pricingTime>
                                                            <instrument>
                                                                <period>Month</period>
                                                                <startDate>2014-12-01</startDate>
                                                                <endDate>2014-12-31</endDate>
                                                                <type>Forward</type>
                                                                <price>0.25852</price>
                                                                <priceOut>r</priceOut>
                                                                <contributors>15</contributors>
                                                            </instrument>
                                                        </underlier>
                                                                                                                <underlier>
                                                            <name>CO2 CER</name>
                                                            <group>European Emissions</group>
                                                            <units>EUR / MT</units>
                                                            <pricingTime>LDN 17:00</pricingTime>
                                                            <instrument>
                                                                <period>Month</period>
                                                                <startDate>2014-12-01</startDate>
                                                                <endDate>2014-12-31</endDate>
                                                                <type>Forward</type>
                                                                <price>0.25852</price>
                                                                <priceOut>r</priceOut>
                                                                <contributors>15</contributors>
                                                            </instrument>
                                                        </underlier>
                                                    </energy>
                                                </results>
                                            </totem>'
                                            ))
                COLUMNS     valuationDate        varchar2(500)          PATH 'n1:valuationDate', 
                                   clientID             varchar2(500)          PATH 'n1:clientID', 
                                   name                 varchar2(500)          PATH 'n1:energy/n1:underlier/n1:name',
                                   group1               varchar2(500)        PATH 'n1:energy/n1:underlier/n1:group',
                                   units                varchar2(500)          PATH 'n1:energy/n1:underlier/n1:units',
                                   pricingTime          varchar2(500)          PATH 'n1:energy/n1:underlier/n1:pricingTime',   
                                   period               varchar2(500)          PATH 'n1:energy/n1:underlier/n1:instrument/n1:period',  
                                   startDate            varchar2(500)          PATH 'n1:energy/n1:underlier/n1:instrument/n1:startDate',  
                                   endDate              varchar2(500)          PATH 'n1:energy/n1:underlier/n1:instrument/n1:endDate',
                                   type                 varchar2(500)          PATH 'n1:energy/n1:underlier/n1:instrument/n1:type',
                                   price                varchar2(500)          PATH 'n1:energy/n1:underlier/n1:instrument/n1:price',
                                   priceOut             varchar2(500)          PATH 'n1:energy/n1:underlier/n1:instrument/n1:priceOut',
                                   contributors         varchar2(500)          PATH 'n1:energy/n1:underlier/n1:instrument/n1:contributors'
   ) AS instrument 

o/p is:

XMLNLS_NS   VALUATIONDATE   CLIENTID    NAME    GROUP1  PERIOD  STARTDATE   ENDDATE TYPE    PRICE   PRICEOUT    CONTRIBUTORS
            2014-07-31      220     

How can I extract/populate values for rest of the tags ?

Edit: Thanks. But when I have more than one underliner, I get following error ORA-19279: XPTY0004 - XQuery dynamic type mismatch: expected singleton sequence - got multi-item sequence

Answer 1

Use the correct namespace. Namespaces are inherited, so basically all your elements here are in the http://www.cool.com/totem/1.1 namespace. For example,

n1:results/energy/underlier/name

should be

n1:results/n1:energy/n1:underlier/n1:name