Home » Developer & Programmer » JDeveloper, Java & XML » Extracting sub nodes from a XML with multiple nodes (11g)
Extracting sub nodes from a XML with multiple nodes [message #528974] Thu, 27 October 2011 15:56 Go to next message
suedemby
Messages: 9
Registered: October 2011
Location: USA
Junior Member
I am new to XML. After spending a lots time in reading, still can't figure out how to extract sub nodes from multi nodes in this file. Really appreciate your help.

<?xml version="1.0" encoding="utf-8"?>
<Orders>
  <LeasingCoNum>123456</LeasingCoNum>
  <DateTime>10/05/2011 10:00:00 AM</DateTime>
  <NewOrders>
    <Order>
      <CustomerPONum>N02345 </CustomerPONum>
      <FleetAccountNum>567890</FleetAccountNum>
      <ModelYear>2011</ModelYear>
      <ModelCode>BFA</ModelCode>
      <OptionCode>01</OptionCode>
      <Spec>C</Spec>
      <ExteriorColorCode>BLK</ExteriorColorCode>
      <InteriorColorCode>LK4</InteriorColorCode>
      <OrderDate>2010-12-14</OrderDate>
      <Qty>1</Qty>
      <OrderedOptions>
        <Code>D9D</Code>
        <Code>M9F</Code>
      </OrderedOptions>
      <ShipToDealerCode>aaaaaa</ShipToDealerCode>
      <SoldToDealerCode>bbbbbb</SoldToDealerCode>
    </Order>
    <Order>
      <CustomerPONum>V12345 </CustomerPONum>
      <FleetAccountNum>123456</FleetAccountNum>
      <ModelYear>2011</ModelYear>
      <ModelCode>BFA</ModelCode>
      <OptionCode>01</OptionCode>
      <Spec>C</Spec>
      <ExteriorColorCode>BLK</ExteriorColorCode>
      <InteriorColorCode>LP9</InteriorColorCode>
      <OrderDate>2011-12-14</OrderDate>
      <Qty>1</Qty>
      <OrderedOptions>
        <Code>D9D</Code>
        <Code>M9F</Code>
      </OrderedOptions>
      <ShipToDealerCode>aaaaaa</ShipToDealerCode>
      <SoldToDealerCode>bbbbbb</SoldToDealerCode>
    </Order>
  </NewOrders>
</Orders>



[mod-edit: code tags added by bb; next time please add them yourself]

[Updated on: Thu, 27 October 2011 22:49] by Moderator

Report message to a moderator

Re: Extracting sub nodes from a XML with multiple nodes [message #528985 is a reply to message #528974] Thu, 27 October 2011 23:19 Go to previous messageGo to next message
Barbara Boehmer
Messages: 9077
Registered: November 2002
Location: California, USA
Senior Member
SCOTT@orcl_11gR2> column leasingconum  format a12
SCOTT@orcl_11gR2> column customerponum format a13
SCOTT@orcl_11gR2> column code	       format a10
SCOTT@orcl_11gR2> with
  2    data as
  3  	 (select xmltype (
  4  '<?xml version="1.0" encoding="utf-8"?>
  5  <Orders>
  6    <LeasingCoNum>123456</LeasingCoNum>
  7    <DateTime>10/05/2011 10:00:00 AM</DateTime>
  8    <NewOrders>
  9  	 <Order>
 10  	   <CustomerPONum>N02345 </CustomerPONum>
 11  	   <FleetAccountNum>567890</FleetAccountNum>
 12  	   <ModelYear>2011</ModelYear>
 13  	   <ModelCode>BFA</ModelCode>
 14  	   <OptionCode>01</OptionCode>
 15  	   <Spec>C</Spec>
 16  	   <ExteriorColorCode>BLK</ExteriorColorCode>
 17  	   <InteriorColorCode>LK4</InteriorColorCode>
 18  	   <OrderDate>2010-12-14</OrderDate>
 19  	   <Qty>1</Qty>
 20  	   <OrderedOptions>
 21  	     <Code>D9D</Code>
 22  	     <Code>M9F</Code>
 23  	   </OrderedOptions>
 24  	   <ShipToDealerCode>aaaaaa</ShipToDealerCode>
 25  	   <SoldToDealerCode>bbbbbb</SoldToDealerCode>
 26  	 </Order>
 27  	 <Order>
 28  	   <CustomerPONum>V12345 </CustomerPONum>
 29  	   <FleetAccountNum>123456</FleetAccountNum>
 30  	   <ModelYear>2011</ModelYear>
 31  	   <ModelCode>BFA</ModelCode>
 32  	   <OptionCode>01</OptionCode>
 33  	   <Spec>C</Spec>
 34  	   <ExteriorColorCode>BLK</ExteriorColorCode>
 35  	   <InteriorColorCode>LP9</InteriorColorCode>
 36  	   <OrderDate>2011-12-14</OrderDate>
 37  	   <Qty>1</Qty>
 38  	   <OrderedOptions>
 39  	     <Code>D9D</Code>
 40  	     <Code>M9F</Code>
 41  	   </OrderedOptions>
 42  	   <ShipToDealerCode>aaaaaa</ShipToDealerCode>
 43  	   <SoldToDealerCode>bbbbbb</SoldToDealerCode>
 44  	 </Order>
 45    </NewOrders>
 46  </Orders>') as col
 47  	  from dual)
 48  select extractvalue (x.column_value, 'Orders/LeasingCoNum')   leasingconum,
 49  	    extractvalue (y.column_value, 'Order/CustomerPONum')   customerponum,
 50  	    extractvalue (z.column_value, 'Code')		   code
 51  from   data t,
 52  	    table (xmlsequence (extract (t.col, 'Orders'))) x,
 53  	    table (xmlsequence (extract (x.column_value, 'Orders/NewOrders/Order'))) y,
 54  	    table (xmlsequence (extract (y.column_value, 'Order/OrderedOptions/Code'))) z
 55  /

LEASINGCONUM CUSTOMERPONUM CODE
------------ ------------- ----------
123456       N02345        D9D
123456       N02345        M9F
123456       V12345        D9D
123456       V12345        M9F

4 rows selected.

SCOTT@orcl_11gR2>

Re: Extracting sub nodes from a XML with multiple nodes [message #528986 is a reply to message #528985] Thu, 27 October 2011 23:35 Go to previous messageGo to next message
suedemby
Messages: 9
Registered: October 2011
Location: USA
Junior Member
This is awesome!! You have cured my three-days headache. Now I only need to create a function to concatenate values in the column CODE group by CUSTOMERPONUM. Thank you so much!!
Re: Extracting sub nodes from a XML with multiple nodes [message #528987 is a reply to message #528986] Thu, 27 October 2011 23:40 Go to previous messageGo to next message
Barbara Boehmer
Messages: 9077
Registered: November 2002
Location: California, USA
Senior Member
Here is an alternate 11g syntax that has a littler more flexibility, as you can specify column types and sizes.

SCOTT@orcl_11gR2> with
  2    data as
  3  	 (select xmltype (
  4  '<?xml version="1.0" encoding="utf-8"?>
  5  <Orders>
  6    <LeasingCoNum>123456</LeasingCoNum>
  7    <DateTime>10/05/2011 10:00:00 AM</DateTime>
  8    <NewOrders>
  9  	 <Order>
 10  	   <CustomerPONum>N02345 </CustomerPONum>
 11  	   <FleetAccountNum>567890</FleetAccountNum>
 12  	   <ModelYear>2011</ModelYear>
 13  	   <ModelCode>BFA</ModelCode>
 14  	   <OptionCode>01</OptionCode>
 15  	   <Spec>C</Spec>
 16  	   <ExteriorColorCode>BLK</ExteriorColorCode>
 17  	   <InteriorColorCode>LK4</InteriorColorCode>
 18  	   <OrderDate>2010-12-14</OrderDate>
 19  	   <Qty>1</Qty>
 20  	   <OrderedOptions>
 21  	     <Code>D9D</Code>
 22  	     <Code>M9F</Code>
 23  	   </OrderedOptions>
 24  	   <ShipToDealerCode>aaaaaa</ShipToDealerCode>
 25  	   <SoldToDealerCode>bbbbbb</SoldToDealerCode>
 26  	 </Order>
 27  	 <Order>
 28  	   <CustomerPONum>V12345 </CustomerPONum>
 29  	   <FleetAccountNum>123456</FleetAccountNum>
 30  	   <ModelYear>2011</ModelYear>
 31  	   <ModelCode>BFA</ModelCode>
 32  	   <OptionCode>01</OptionCode>
 33  	   <Spec>C</Spec>
 34  	   <ExteriorColorCode>BLK</ExteriorColorCode>
 35  	   <InteriorColorCode>LP9</InteriorColorCode>
 36  	   <OrderDate>2011-12-14</OrderDate>
 37  	   <Qty>1</Qty>
 38  	   <OrderedOptions>
 39  	     <Code>D9D</Code>
 40  	     <Code>M9F</Code>
 41  	   </OrderedOptions>
 42  	   <ShipToDealerCode>aaaaaa</ShipToDealerCode>
 43  	   <SoldToDealerCode>bbbbbb</SoldToDealerCode>
 44  	 </Order>
 45    </NewOrders>
 46  </Orders>') as col
 47  	  from dual)
 48  select x.leasingconum, y.customerponum, z.code
 49  from   data t,
 50  	    xmltable
 51  	      ('/Orders'
 52  	       passing t.col
 53  	       columns
 54  		 column_value xmltype	   path '/Orders',
 55  		 leasingconum varchar2(12) path '/Orders/LeasingCoNum') x,
 56  	    xmltable
 57  	      ('/Orders/NewOrders/Order'
 58  	       passing x.column_value
 59  	       columns
 60  		 column_value  xmltype	    path '/Order',
 61  		 customerponum varchar2(13) path '/Order/CustomerPONum') y,
 62  	    xmltable
 63  	      ('/Order/OrderedOptions/Code'
 64  	       passing y.column_value
 65  	       columns
 66  		 code	       varchar2(10) path '/Code') z
 67  /

LEASINGCONUM CUSTOMERPONUM CODE
------------ ------------- ----------
123456       N02345        D9D
123456       N02345        M9F
123456       V12345        D9D
123456       V12345        M9F

4 rows selected.

SCOTT@orcl_11gR2>

Re: Extracting sub nodes from a XML with multiple nodes [message #528989 is a reply to message #528986] Thu, 27 October 2011 23:51 Go to previous messageGo to next message
Barbara Boehmer
Messages: 9077
Registered: November 2002
Location: California, USA
Senior Member
suedemby wrote on Thu, 27 October 2011 21:35


Now I only need to create a function to concatenate values in the column CODE group by CUSTOMERPONUM.


You can use any of various string aggregation techniques, such as listagg in the demonstrations below.

SCOTT@orcl_11gR2> column leasingconum  format a12
SCOTT@orcl_11gR2> column customerponum format a13
SCOTT@orcl_11gR2> column codes	       format a20
SCOTT@orcl_11gR2> with
  2    data as
  3  	 (select xmltype (
  4  '<?xml version="1.0" encoding="utf-8"?>
  5  <Orders>
  6    <LeasingCoNum>123456</LeasingCoNum>
  7    <DateTime>10/05/2011 10:00:00 AM</DateTime>
  8    <NewOrders>
  9  	 <Order>
 10  	   <CustomerPONum>N02345 </CustomerPONum>
 11  	   <FleetAccountNum>567890</FleetAccountNum>
 12  	   <ModelYear>2011</ModelYear>
 13  	   <ModelCode>BFA</ModelCode>
 14  	   <OptionCode>01</OptionCode>
 15  	   <Spec>C</Spec>
 16  	   <ExteriorColorCode>BLK</ExteriorColorCode>
 17  	   <InteriorColorCode>LK4</InteriorColorCode>
 18  	   <OrderDate>2010-12-14</OrderDate>
 19  	   <Qty>1</Qty>
 20  	   <OrderedOptions>
 21  	     <Code>D9D</Code>
 22  	     <Code>M9F</Code>
 23  	   </OrderedOptions>
 24  	   <ShipToDealerCode>aaaaaa</ShipToDealerCode>
 25  	   <SoldToDealerCode>bbbbbb</SoldToDealerCode>
 26  	 </Order>
 27  	 <Order>
 28  	   <CustomerPONum>V12345 </CustomerPONum>
 29  	   <FleetAccountNum>123456</FleetAccountNum>
 30  	   <ModelYear>2011</ModelYear>
 31  	   <ModelCode>BFA</ModelCode>
 32  	   <OptionCode>01</OptionCode>
 33  	   <Spec>C</Spec>
 34  	   <ExteriorColorCode>BLK</ExteriorColorCode>
 35  	   <InteriorColorCode>LP9</InteriorColorCode>
 36  	   <OrderDate>2011-12-14</OrderDate>
 37  	   <Qty>1</Qty>
 38  	   <OrderedOptions>
 39  	     <Code>D9D</Code>
 40  	     <Code>M9F</Code>
 41  	   </OrderedOptions>
 42  	   <ShipToDealerCode>aaaaaa</ShipToDealerCode>
 43  	   <SoldToDealerCode>bbbbbb</SoldToDealerCode>
 44  	 </Order>
 45    </NewOrders>
 46  </Orders>') as col
 47  	  from dual),
 48    columns as
 49  	 (select extractvalue (x.column_value, 'Orders/LeasingCoNum')	leasingconum,
 50  		 extractvalue (y.column_value, 'Order/CustomerPONum')	customerponum,
 51  		 extractvalue (z.column_value, 'Code')			code
 52  	  from	 data t,
 53  		 table (xmlsequence (extract (t.col, 'Orders'))) x,
 54  		 table (xmlsequence (extract (x.column_value, 'Orders/NewOrders/Order'))) y,
 55  		 table (xmlsequence (extract (y.column_value, 'Order/OrderedOptions/Code'))) z)
 56  select leasingconum, customerponum,
 57  	    listagg (code, ',') within group (order by code) codes
 58  from   columns
 59  group  by leasingconum, customerponum
 60  /

LEASINGCONUM CUSTOMERPONUM CODES
------------ ------------- --------------------
123456       N02345        D9D,M9F
123456       V12345        D9D,M9F

2 rows selected.


SCOTT@orcl_11gR2> column codes	       format a20
SCOTT@orcl_11gR2> with
  2    data as
  3  	 (select xmltype (
  4  '<?xml version="1.0" encoding="utf-8"?>
  5  <Orders>
  6    <LeasingCoNum>123456</LeasingCoNum>
  7    <DateTime>10/05/2011 10:00:00 AM</DateTime>
  8    <NewOrders>
  9  	 <Order>
 10  	   <CustomerPONum>N02345 </CustomerPONum>
 11  	   <FleetAccountNum>567890</FleetAccountNum>
 12  	   <ModelYear>2011</ModelYear>
 13  	   <ModelCode>BFA</ModelCode>
 14  	   <OptionCode>01</OptionCode>
 15  	   <Spec>C</Spec>
 16  	   <ExteriorColorCode>BLK</ExteriorColorCode>
 17  	   <InteriorColorCode>LK4</InteriorColorCode>
 18  	   <OrderDate>2010-12-14</OrderDate>
 19  	   <Qty>1</Qty>
 20  	   <OrderedOptions>
 21  	     <Code>D9D</Code>
 22  	     <Code>M9F</Code>
 23  	   </OrderedOptions>
 24  	   <ShipToDealerCode>aaaaaa</ShipToDealerCode>
 25  	   <SoldToDealerCode>bbbbbb</SoldToDealerCode>
 26  	 </Order>
 27  	 <Order>
 28  	   <CustomerPONum>V12345 </CustomerPONum>
 29  	   <FleetAccountNum>123456</FleetAccountNum>
 30  	   <ModelYear>2011</ModelYear>
 31  	   <ModelCode>BFA</ModelCode>
 32  	   <OptionCode>01</OptionCode>
 33  	   <Spec>C</Spec>
 34  	   <ExteriorColorCode>BLK</ExteriorColorCode>
 35  	   <InteriorColorCode>LP9</InteriorColorCode>
 36  	   <OrderDate>2011-12-14</OrderDate>
 37  	   <Qty>1</Qty>
 38  	   <OrderedOptions>
 39  	     <Code>D9D</Code>
 40  	     <Code>M9F</Code>
 41  	   </OrderedOptions>
 42  	   <ShipToDealerCode>aaaaaa</ShipToDealerCode>
 43  	   <SoldToDealerCode>bbbbbb</SoldToDealerCode>
 44  	 </Order>
 45    </NewOrders>
 46  </Orders>') as col
 47  	  from dual),
 48    columns as
 49  	 (select x.leasingconum, y.customerponum, z.code
 50  	  from	 data t,
 51  		 xmltable
 52  		   ('/Orders'
 53  		    passing t.col
 54  		    columns
 55  		      column_value xmltype	path '/Orders',
 56  		      leasingconum varchar2(12) path '/Orders/LeasingCoNum') x,
 57  		 xmltable
 58  		   ('/Orders/NewOrders/Order'
 59  		    passing x.column_value
 60  		    columns
 61  		      column_value  xmltype	 path '/Order',
 62  		      customerponum varchar2(13) path '/Order/CustomerPONum') y,
 63  		 xmltable
 64  		   ('/Order/OrderedOptions/Code'
 65  		    passing y.column_value
 66  		    columns
 67  		      code	    varchar2(10) path '/Code') z)
 68  select leasingconum, customerponum,
 69  	    listagg (code, ',') within group (order by code) codes
 70  from   columns
 71  group  by leasingconum, customerponum
 72  /

LEASINGCONUM CUSTOMERPONUM CODES
------------ ------------- --------------------
123456       N02345        D9D,M9F
123456       V12345        D9D,M9F

2 rows selected.

SCOTT@orcl_11gR2>

Re: Extracting sub nodes from a XML with multiple nodes [message #528990 is a reply to message #528989] Fri, 28 October 2011 00:45 Go to previous messageGo to next message
suedemby
Messages: 9
Registered: October 2011
Location: USA
Junior Member
The code works when I am using the xmltype(string). However,I inserted the xml document into the table temp (xml_doc xmltype), and got an error when running this:
WITH data AS (SELECT xml_doc FROM temp),
     columns
         AS (SELECT EXTRACTVALUE (x.COLUMN_VALUE, 'Orders/LeasingCoNum')
                        leasingconum,
                    EXTRACTVALUE (y.COLUMN_VALUE, 'Order/CustomerPONum')
                        customerponum,
                    EXTRACTVALUE (z.COLUMN_VALUE, 'Code') code
               FROM data t,
                    TABLE (XMLSEQUENCE (EXTRACT (t.xml_doc, 'Orders'))) x,
                    TABLE (
                        XMLSEQUENCE (
                            EXTRACT (x.COLUMN_VALUE,
                                     'Orders/NewOrders/Order'))) y,
                    TABLE (
                        XMLSEQUENCE (
                            EXTRACT (y.COLUMN_VALUE,
                                     'Order/OrderedOptions/Code'))) z)
SELECT leasingconum,
       customerponum,
       listagg (code, ',') WITHIN GROUP (ORDER BY code) codes
  FROM columns
GROUP BY leasingconum, customerponum;

[1]: ORA-00600: internal error code, arguments: [kkoljt1], [], [], [], [], [], [], [], [], [], [], []

[Updated on: Fri, 28 October 2011 00:49]

Report message to a moderator

Re: Extracting sub nodes from a XML with multiple nodes [message #528997 is a reply to message #528990] Fri, 28 October 2011 01:34 Go to previous messageGo to next message
Michel Cadot
Messages: 68625
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
ORA-00600/ORA-07445/ORA-03113 = Oracle bug => search on Metalink and/or call Oracle support
Have a look at alert.log and trace files.
You can also read this article: Troubleshooting Internal Errors.

Regards
Michel
Re: Extracting sub nodes from a XML with multiple nodes [message #529077 is a reply to message #528997] Fri, 28 October 2011 10:23 Go to previous messageGo to next message
suedemby
Messages: 9
Registered: October 2011
Location: USA
Junior Member
I found another way (external file) to make it work without Oracle error. without your help, I might still pulling my hair. Thank you so much!!
WITH DATA  AS (SELECT xmltype(BFILENAME('XML_DATA', 'Subaru10142011.xml'),nls_charset_id('AL32UTF8')) xml_doc
            FROM dual),
             
      columns AS 
        (select 
          extractvalue (x.column_value, 'Orders/LeasingCoNum')   leasingconum,
          extractvalue (x.column_value, 'Orders/DateTime')       DateTime,
          extractvalue (y.column_value, 'Order/CustomerPONum')   customerponum,
          extractvalue (y.column_value, 'Order/FleetAccountNum') FleetAccountNum,
          extractvalue (y.column_value, 'Order/ModelYear') ModelYear,
          extractvalue (y.column_value, 'Order/ModelCode') ModelCode,
          extractvalue (y.column_value, 'Order/OptionCode') OptionCode,
          extractvalue (y.column_value, 'Order/Spec') Spec,
          extractvalue (y.column_value, 'Order/ExteriorColorCode') ExteriorColorCode,
          extractvalue (y.column_value, 'Order/InteriorColorCode') InteriorColorCode,          
          extractvalue (z.column_value, 'Code')          code
        from   data t,
              table (xmlsequence (extract (t.xml_doc, 'Orders'))) x,
              table (xmlsequence (extract (x.column_value, 'Orders/NewOrders/Order'))) y,
              table (xmlsequence (extract (y.column_value, 'Order/OrderedOptions/Code'))) z
          )
select leasingconum,DateTime, customerponum,FleetAccountNum,ModelYear,ModelCode,OptionCode,Spec
    ,ExteriorColorCode,InteriorColorCode,
         listagg (code, ',') within group (order by code) codes
  from   columns
  group  by leasingconum,DateTime, customerponum,FleetAccountNum,ModelYear,ModelCode,OptionCode,Spec
    ,ExteriorColorCode,InteriorColorCode;
Re: Extracting sub nodes from a XML with multiple nodes [message #529100 is a reply to message #528990] Fri, 28 October 2011 12:45 Go to previous messageGo to next message
Barbara Boehmer
Messages: 9077
Registered: November 2002
Location: California, USA
Senior Member
I did not get the error that you got, as shown below, so either there is a difference in our versions or you inserted it differently or there is some other difference in our systems.

SCOTT@orcl_11gR2> select * from v$version
  2  /

BANNER
--------------------------------------------------------------------------------
Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - 64bit Production
PL/SQL Release 11.2.0.1.0 - Production
CORE	11.2.0.1.0	Production
TNS for 64-bit Windows: Version 11.2.0.1.0 - Production
NLSRTL Version 11.2.0.1.0 - Production

5 rows selected.

SCOTT@orcl_11gR2> create table temp
  2    (xml_doc  xmltype)
  3  /

Table created.

SCOTT@orcl_11gR2> insert into temp values (xmltype (
  2  '<?xml version="1.0" encoding="utf-8"?>
  3  <Orders>
  4    <LeasingCoNum>123456</LeasingCoNum>
  5    <DateTime>10/05/2011 10:00:00 AM</DateTime>
  6    <NewOrders>
  7  	 <Order>
  8  	   <CustomerPONum>N02345 </CustomerPONum>
  9  	   <FleetAccountNum>567890</FleetAccountNum>
 10  	   <ModelYear>2011</ModelYear>
 11  	   <ModelCode>BFA</ModelCode>
 12  	   <OptionCode>01</OptionCode>
 13  	   <Spec>C</Spec>
 14  	   <ExteriorColorCode>BLK</ExteriorColorCode>
 15  	   <InteriorColorCode>LK4</InteriorColorCode>
 16  	   <OrderDate>2010-12-14</OrderDate>
 17  	   <Qty>1</Qty>
 18  	   <OrderedOptions>
 19  	     <Code>D9D</Code>
 20  	     <Code>M9F</Code>
 21  	   </OrderedOptions>
 22  	   <ShipToDealerCode>aaaaaa</ShipToDealerCode>
 23  	   <SoldToDealerCode>bbbbbb</SoldToDealerCode>
 24  	 </Order>
 25  	 <Order>
 26  	   <CustomerPONum>V12345 </CustomerPONum>
 27  	   <FleetAccountNum>123456</FleetAccountNum>
 28  	   <ModelYear>2011</ModelYear>
 29  	   <ModelCode>BFA</ModelCode>
 30  	   <OptionCode>01</OptionCode>
 31  	   <Spec>C</Spec>
 32  	   <ExteriorColorCode>BLK</ExteriorColorCode>
 33  	   <InteriorColorCode>LP9</InteriorColorCode>
 34  	   <OrderDate>2011-12-14</OrderDate>
 35  	   <Qty>1</Qty>
 36  	   <OrderedOptions>
 37  	     <Code>D9D</Code>
 38  	     <Code>M9F</Code>
 39  	   </OrderedOptions>
 40  	   <ShipToDealerCode>aaaaaa</ShipToDealerCode>
 41  	   <SoldToDealerCode>bbbbbb</SoldToDealerCode>
 42  	 </Order>
 43    </NewOrders>
 44  </Orders>'
 45  ))
 46  /

1 row created.

SCOTT@orcl_11gR2> column leasingconum  format a12
SCOTT@orcl_11gR2> column customerponum format a13
SCOTT@orcl_11gR2> column codes	       format a20
SCOTT@orcl_11gR2> WITH data AS (SELECT xml_doc FROM temp),
  2  	  columns
  3  	      AS (SELECT EXTRACTVALUE (x.COLUMN_VALUE, 'Orders/LeasingCoNum')
  4  			     leasingconum,
  5  			 EXTRACTVALUE (y.COLUMN_VALUE, 'Order/CustomerPONum')
  6  			     customerponum,
  7  			 EXTRACTVALUE (z.COLUMN_VALUE, 'Code') code
  8  		    FROM data t,
  9  			 TABLE (XMLSEQUENCE (EXTRACT (t.xml_doc, 'Orders'))) x,
 10  			 TABLE (
 11  			     XMLSEQUENCE (
 12  				 EXTRACT (x.COLUMN_VALUE,
 13  					  'Orders/NewOrders/Order'))) y,
 14  			 TABLE (
 15  			     XMLSEQUENCE (
 16  				 EXTRACT (y.COLUMN_VALUE,
 17  					  'Order/OrderedOptions/Code'))) z)
 18  SELECT leasingconum,
 19  	    customerponum,
 20  	    listagg (code, ',') WITHIN GROUP (ORDER BY code) codes
 21    FROM columns
 22  GROUP BY leasingconum, customerponum;

LEASINGCONUM CUSTOMERPONUM CODES
------------ ------------- --------------------
123456       N02345        D9D,M9F
123456       V12345        D9D,M9F

2 rows selected.

SCOTT@orcl_11gR2>

Re: Extracting sub nodes from a XML with multiple nodes [message #529111 is a reply to message #529100] Fri, 28 October 2011 13:49 Go to previous message
suedemby
Messages: 9
Registered: October 2011
Location: USA
Junior Member
May be the 11g version is different? Here is the enviroment I am working in:
select * from v$version
BANNER                                                                      
----------------------------------------------------------------------------
Oracle Database 11g Enterprise Edition Release 11.2.0.2.0 - 64bit Production
PL/SQL Release 11.2.0.2.0 - Production                                      
CORE    11.2.0.2.0  Production                                                  
TNS for Solaris: Version 11.2.0.2.0 - Production                            
NLSRTL Version 11.2.0.2.0 - Production    
Previous Topic: What is exactly the MDS repository
Next Topic: XMLQuery - extract document version
Goto Forum:
  


Current Time: Thu Mar 28 14:34:46 CDT 2024