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  |
 |
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  |
 |
Barbara Boehmer
Messages: 9103
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 #528987 is a reply to message #528986] |
Thu, 27 October 2011 23:40 |
|
Barbara Boehmer
Messages: 9103
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 |
|
Barbara Boehmer
Messages: 9103
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 |
|
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 #529077 is a reply to message #528997] |
Fri, 28 October 2011 10:23 |
|
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 |
|
Barbara Boehmer
Messages: 9103
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 |
|
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
|
|
|
|
Goto Forum:
Current Time: Sun Jan 26 01:01:11 CST 2025
|
] 
Blog
Search
Help
Members
Register
Login
Home
