title: Oracle DB - Extract XML data using XMLTABLE
body: <p>I am using XMLTABLE Function in Oracle 10g database and want to extract values from XML into Oracle Table. </p>
  
  <pre><code> SELECT * 
   FROM xmltable(
                  xmlnamespaces ('http://www.cool.com/totem/1.1' AS  "n1"), '/n1:totem/n1:results' 
                  PASSING xmltype.createxml(('&lt;?xml version="1.0" encoding="UTF-8" standalone="yes"?&gt;
                                              &lt;totem xmlns="http://www.cool.com/totem/1.1"&gt;
                                                  &lt;results&gt;
                                                      &lt;valuationDate&gt;2014-07-31&lt;/valuationDate&gt;
                                                      &lt;clientID&gt;220&lt;/clientID&gt;
                                                      &lt;energy&gt;
                                                          &lt;underlier&gt;
                                                              &lt;name&gt;CO2 CER&lt;/name&gt;
                                                              &lt;group&gt;European Emissions&lt;/group&gt;
                                                              &lt;units&gt;EUR / MT&lt;/units&gt;
                                                              &lt;pricingTime&gt;LDN 17:00&lt;/pricingTime&gt;
                                                              &lt;instrument&gt;
                                                                  &lt;period&gt;Month&lt;/period&gt;
                                                                  &lt;startDate&gt;2014-12-01&lt;/startDate&gt;
                                                                  &lt;endDate&gt;2014-12-31&lt;/endDate&gt;
                                                                  &lt;type&gt;Forward&lt;/type&gt;
                                                                  &lt;price&gt;0.25852&lt;/price&gt;
                                                                  &lt;priceOut&gt;r&lt;/priceOut&gt;
                                                                  &lt;contributors&gt;15&lt;/contributors&gt;
                                                              &lt;/instrument&gt;
                                                          &lt;/underlier&gt;
                                                                                                                  &lt;underlier&gt;
                                                              &lt;name&gt;CO2 CER&lt;/name&gt;
                                                              &lt;group&gt;European Emissions&lt;/group&gt;
                                                              &lt;units&gt;EUR / MT&lt;/units&gt;
                                                              &lt;pricingTime&gt;LDN 17:00&lt;/pricingTime&gt;
                                                              &lt;instrument&gt;
                                                                  &lt;period&gt;Month&lt;/period&gt;
                                                                  &lt;startDate&gt;2014-12-01&lt;/startDate&gt;
                                                                  &lt;endDate&gt;2014-12-31&lt;/endDate&gt;
                                                                  &lt;type&gt;Forward&lt;/type&gt;
                                                                  &lt;price&gt;0.25852&lt;/price&gt;
                                                                  &lt;priceOut&gt;r&lt;/priceOut&gt;
                                                                  &lt;contributors&gt;15&lt;/contributors&gt;
                                                              &lt;/instrument&gt;
                                                          &lt;/underlier&gt;
                                                      &lt;/energy&gt;
                                                  &lt;/results&gt;
                                              &lt;/totem&gt;'
                                              ))
                  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 
  </code></pre>
  
  <p>o/p is: </p>
  
  <pre><code>XMLNLS_NS   VALUATIONDATE   CLIENTID    NAME    GROUP1  PERIOD  STARTDATE   ENDDATE TYPE    PRICE   PRICEOUT    CONTRIBUTORS
              2014-07-31      220     
  </code></pre>
  
  <p>How can I extract/populate values for rest of the tags ?</p>
  
  <p>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 </p>