Error while extracting data from XML in Oracle

Question

I am using oracle 10g and the query below results in following error:

ORA-19279: XPTY0004 - XQuery dynamic type mismatch: expected singleton sequence - got multi-item sequence 

How can we extract all elements in case of multiple items in XML ? Basically I am interested to separate all underlier instrument wise.

 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 SPOT</name>
                            <group>European Emissions</group>
                            <units>EUR / MT</units>
                            <pricingTime>LDN 17:00</pricingTime>
                            <instrument>
                                <period>Month</period>
                                <startDate>2014-07-31</startDate>
                                <endDate>2014-07-31</endDate>
                                <type>Spot</type>
                                <price>0.17</price>
                                <consensusPrice>0.165988</consensusPrice>
                                <compositePrice>0.165988</compositePrice>
                                <priceRange>0.01</priceRange>
                                <priceStddev>0.004685</priceStddev>
                                <contributors>10</contributors>
                            </instrument>
                        </underlier>
                        <underlier>
                            <name>CO2 EUA</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>6.251277</price>
                                <consensusPrice>6.220854</consensusPrice>
                                <compositePrice>6.220854</compositePrice>
                                <priceOut>*</priceOut>
                                <priceRange>0.044779</priceRange>
                                <priceStddev>0.014507</priceStddev>
                                <contributors>18</contributors>
                            </instrument>
                            <instrument>
                                <period>Month</period>
                                <startDate>2015-12-01</startDate>
                                <endDate>2015-12-31</endDate>
                                <type>Forward</type>
                                <price>6.399921</price>
                                <consensusPrice>6.382712</consensusPrice>
                                <compositePrice>6.382092</compositePrice>
                                <priceOut>s</priceOut>
                                <priceRange>0.05</priceRange>
                                <priceStddev>0.014458</priceStddev>
                                <contributors>18</contributors>
                            </instrument>
                            <instrument>
                                <period>Month</period>
                                <startDate>2016-12-01</startDate>
                                <endDate>2016-12-31</endDate>
                                <type>Forward</type>
                                <price>6.590721</price>
                                <consensusPrice>6.563745</consensusPrice>
                                <compositePrice>6.563749</compositePrice>
                                <priceOut>s</priceOut>
                                <priceRange>0.070721</priceRange>
                                <priceStddev>0.017205</priceStddev>
                                <contributors>18</contributors>
                            </instrument>
                        </underlier>
                        <underlier>
                            <name>CO2 EUA SPOT</name>
                            <group>European Emissions</group>
                            <units>EUR / MT</units>
                            <pricingTime>LDN 17:00</pricingTime>
                            <instrument>
                                <period>Month</period>
                                <startDate>2014-07-31</startDate>
                                <endDate>2014-07-31</endDate>
                                <type>Spot</type>
                                <price>6.19</price>
                                <consensusPrice>6.186412</consensusPrice>
                                <compositePrice>6.186412</compositePrice>
                                <priceRange>0.0113</priceRange>
                                <priceStddev>0.004984</priceStddev>
                                <contributors>10</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 ;

Answer 1

In your xml, the data is at multiple levels. There are multiple underlier tags and each can have more than one instrument tags. So, you should break it down in stages. (Your XML was too long for varchar datatype, so I shortened it.)

Query:

SELECT 
    tab1.valuationDate, 
    tab1.clientid, 
    tab2.name, 
    tab2.group1,
    tab2.units, 
    tab2.pricingtime,
    tab3.period,        
    tab3.startdate, 
    tab3.enddate,       
    tab3.type,      
    tab3.price,     
    tab3.priceout,  
    tab3.contributors   
FROM
    XMLTABLE(
    xmlnamespaces ('http://www.cool.com/totem/1.1' AS  "n1"), '/n1:totem/n1:results' 
    passing XMLTYPE(
               '<?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 EUA</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>6.251277</price>
                                        <consensusPrice>6.220854</consensusPrice>
                                        <compositePrice>6.220854</compositePrice>
                                        <priceOut>*</priceOut>
                                        <priceRange>0.044779</priceRange>
                                        <priceStddev>0.014507</priceStddev>
                                        <contributors>18</contributors>
                                    </instrument>
                                    <instrument>
                                        <period>Month</period>
                                        <startDate>2015-12-01</startDate>
                                        <endDate>2015-12-31</endDate>
                                        <type>Forward</type>
                                        <price>6.399921</price>
                                        <consensusPrice>6.382712</consensusPrice>
                                        <compositePrice>6.382092</compositePrice>
                                        <priceOut>s</priceOut>
                                        <priceRange>0.05</priceRange>
                                        <priceStddev>0.014458</priceStddev>
                                        <contributors>18</contributors>
                                    </instrument>
                                </underlier>
                        </energy>
                        </results>
                    </totem>'
                    )
    COLUMNS
            valuationDate   VARCHAR2(10)    PATH    'n1:valuationDate',
            clientid    VARCHAR2(10)    PATH    'n1:clientID',
            underlier   XMLTYPE     PATH    'n1:energy/n1:underlier'
            ) tab1,
    XMLTABLE(
        xmlnamespaces ('http://www.cool.com/totem/1.1' AS  "n1"), '/n1:underlier' 
        passing tab1.underlier
    COLUMNS
            name        VARCHAR2(20)    PATH    'n1:name',
            group1      VARCHAR2(30)    PATH    'n1:group',
            units       VARCHAR2(10)    PATH    'n1:units',
            pricingtime VARCHAR2(20)    PATH    'n1:pricingTime',
            instrument  XMLTYPE     PATH    'n1:instrument'
        ) tab2,
    XMLTABLE(
        xmlnamespaces ('http://www.cool.com/totem/1.1' AS  "n1"), '/n1:instrument' 
        passing tab2.instrument
    COLUMNS 
            period      VARCHAR2(10)    PATH    'n1:period',
            startdate   VARCHAR2(10)    PATH    'n1:startDate',
            enddate     VARCHAR2(10)    PATH    'n1:endDate',
            type        VARCHAR2(10)    PATH    'n1:type',
            price       NUMBER      PATH    'n1:price',
            priceout    VARCHAR2(10)    PATH    'n1:priceOut',
            contributors    VARCHAR2(12)    PATH    'n1:contributors'
        ) tab3;

Result:

VALUATIOND CLIENTID   NAME                 GROUP1                         UNITS      PRICINGTIME          PERIOD     STARTDATE  ENDDATE    TYPE            PRICE PRICEOUT   CONTRIBUTORS
---------- ---------- -------------------- ------------------------------ ---------- -------------------- ---------- ---------- ---------- ---------- ---------- ---------- ------------
2014-07-31 220        CO2 CER              European Emissions             EUR / MT   LDN 17:00            Month      2014-12-01 2014-12-31 Forward        .25852 r          15
2014-07-31 220        CO2 EUA              European Emissions             EUR / MT   LDN 17:00            Month      2014-12-01 2014-12-31 Forward      6.251277 *          18
2014-07-31 220        CO2 EUA              European Emissions             EUR / MT   LDN 17:00            Month      2015-12-01 2015-12-31 Forward      6.399921 s          18