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

dirkk · Accepted Answer · 2014-11-12 14:07:57Z

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

answered Nov 12, 2014 at 14:07

dirkk

6,2285 gold badges35 silver badges52 bronze badges

Sign up to request clarification or add additional context in comments.

4 Comments

James Bond Over a year ago

Thanks Dirkk ... I still have a trouble, when I have more than one underlier, I get foll. error: ORA-19279: XPTY0004 - XQuery dynamic type mismatch: expected singleton sequence - got multi-item sequence

dirkk Over a year ago

well, as the error message and you yourself say: You have multiple underlier elements, but Oracle expects one. I can't now which one you need. If you just want the first one, user underlier[1]

James Bond Over a year ago

How can we get all of them ?

dirkk Over a year ago

How do you want to put multiple elements in one output? You need to be more specific what you want to get as result. However, this is a totally different question and you should ask a new question. But please search before, because I am pretty sure this has been asked before.

Collectives™ on Stack Overflow

Oracle DB - Extract XML data using XMLTABLE

1 Answer 1

4 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

4 Comments

Your Answer

Sign up or log in

Post as a guest

Related