Home » Developer & Programmer » JDeveloper, Java & XML » SQL to do insert from XML file in external table (10.2.0.3.0)
| SQL to do insert from XML file in external table [message #324509] |
Mon, 02 June 2008 20:22  |
lupeg
Messages: 5
Registered: June 2008
|
Junior Member |
|
|
I'm needing to insert data from a XML file in an external directory into an table. I've got some sql already put together that works but only if the XML has a single node in it. What I need is to put together the sql to get multiple nodes. Here is a sample of what my sql looks like.
INSERT INTO car
(car_id, model, make, year)
SELECT EXTRACTVALUE (column_value, '/CAR/CAR_ID'),
EXTRACTVALUE (column_value, '/CAR/CAR_MODEL'),
EXTRACTVALUE (column_value, '/CAR/CAR_MAKE'),
EXTRACTVALUE (column_value, '/CAR/YEAR')
FROM TABLE (XMLSEQUENCE (XMLTYPE (BFILENAME ('CAR_DIR', 'cars.xml'),
NLS_CHARSET_ID ('WE8ISO8859P1')
)
)
);
This is a sample of what my xml file looks like
<CARS>
<CAR>
<CAR_ID>1000</CAR_ID>
<CAR_MODEL>Accord</CAR_MODEL>
<CAR_MAKE>Honda</CARE_MAKE>
<YEAR>2004</YEAR>
</CAR>
<CAR>
<CAR_ID>1001</CAR_ID>
<CAR_MODEL>Accord</CAR_MODEL>
<CAR_MAKE>Civic</CARE_MAKE>
<YEAR>2002</YEAR>
</CAR>
<CAR>
<CAR_ID>1002</CAR_ID>
<CAR_MODEL>Charger</CAR_MODEL>
<CAR_MAKE>Dodge</CARE_MAKE>
<YEAR>2006</YEAR>
</CAR>
<CAR>
<CAR_ID>1003</CAR_ID>
<CAR_MODEL>Mustang</CAR_MODEL>
<CAR_MAKE>Ford</CARE_MAKE>
<YEAR>1965</YEAR>
</CAR>
<CAR>
<CAR_ID>1004</CAR_ID>
<CAR_MODEL>Camaro</CAR_MODEL>
<CAR_MAKE>Chevrolet</CARE_MAKE>
<YEAR>1969</YEAR>
</CAR>
</CARS>
Any suggestions on how I can make this work?
|
|
|
|
| Re: SQL to do insert from XML file in external table [message #324736 is a reply to message #324509] |
Tue, 03 June 2008 17:17  |
 |
Barbara Boehmer
Messages: 9088
Registered: November 2002
Location: California, USA
|
Senior Member |
|
|
SCOTT@orcl_11g> CREATE TABLE car
2 (car_id NUMBER,
3 model VARCHAR2 (15),
4 make VARCHAR2 (15),
5 year NUMBER)
6 /
Table created.
SCOTT@orcl_11g> CREATE OR REPLACE DIRECTORY car_dir AS 'c:\oracle11g'
2 /
Directory created.
SCOTT@orcl_11g> SELECT *
2 FROM TABLE (XMLSEQUENCE
3 (XMLTYPE
4 (BFILENAME ('CAR_DIR', 'cars.xml'),
5 NLS_CHARSET_ID ('WE8MSWIN1252'))))
6 /
COLUMN_VALUE
--------------------------------------------------------------------------------
<CARS>
<CAR>
<CAR_ID>1000</CAR_ID>
<CAR_MODEL>Accord</CAR_MODEL>
<CAR_MAKE>Honda</CAR_MAKE>
<YEAR>2004</YEAR>
</CAR>
<CAR>
<CAR_ID>1001</CAR_ID>
<CAR_MODEL>Accord</CAR_MODEL>
<CAR_MAKE>Civic</CAR_MAKE>
<YEAR>2002</YEAR>
</CAR>
<CAR>
<CAR_ID>1002</CAR_ID>
<CAR_MODEL>Charger</CAR_MODEL>
<CAR_MAKE>Dodge</CAR_MAKE>
<YEAR>2006</YEAR>
</CAR>
<CAR>
<CAR_ID>1003</CAR_ID>
<CAR_MODEL>Mustang</CAR_MODEL>
<CAR_MAKE>Ford</CAR_MAKE>
<YEAR>1965</YEAR>
</CAR>
<CAR>
<CAR_ID>1004</CAR_ID>
<CAR_MODEL>Camaro</CAR_MODEL>
<CAR_MAKE>Chevrolet</CAR_MAKE>
<YEAR>1969</YEAR>
</CAR>
</CARS>
1 row selected.
SCOTT@orcl_11g> INSERT INTO car (car_id, model, make, year)
2 SELECT EXTRACTVALUE (column_value, '/CARS/CAR[position()=' || rn || ']/CAR_ID'),
3 EXTRACTVALUE (column_value, '/CARS/CAR[position()=' || rn || ']/CAR_MODEL'),
4 EXTRACTVALUE (column_value, '/CARS/CAR[position()=' || rn || ']/CAR_MAKE'),
5 EXTRACTVALUE (column_value, '/CARS/CAR[position()=' || rn || ']/YEAR')
6 FROM TABLE (XMLSEQUENCE
7 (XMLTYPE
8 (BFILENAME ('CAR_DIR', 'cars.xml'),
9 NLS_CHARSET_ID ('WE8MSWIN1252')))),
10 (SELECT ROWNUM rn
11 FROM DUAL
12 CONNECT BY LEVEL <= 10)
13 WHERE EXTRACTVALUE (column_value, '/CARS/CAR[position()=' || rn || ']/CAR_ID') IS NOT NULL
14 /
5 rows created.
SCOTT@orcl_11g> SELECT * FROM car
2 /
CAR_ID MODEL MAKE YEAR
---------- --------------- --------------- ----------
1000 Accord Honda 2004
1001 Accord Civic 2002
1002 Charger Dodge 2006
1003 Mustang Ford 1965
1004 Camaro Chevrolet 1969
5 rows selected.
SCOTT@orcl_11g>
|
|
|
|
|
|
| Re: SQL to do insert from XML file in external table [message #380280 is a reply to message #324737] |
Sat, 10 January 2009 02:52 |
|
Barbara Boehmer
Messages: 9088
Registered: November 2002
Location: California, USA
|
Senior Member |
|
|
I know this is an old thread, but in case anybody finds this by searching, the following is a more efficient method:
SCOTT@orcl_11g> INSERT INTO car (car_id, model, make, year)
2 SELECT extractvalue (column_value, '/CAR/CAR_ID'),
3 extractvalue (column_value, '/CAR/CAR_MODEL'),
4 extractvalue (column_value, '/CAR/CAR_MAKE'),
5 extractvalue (column_value, '/CAR/YEAR')
6 FROM TABLE
7 (XMLSEQUENCE
8 (EXTRACT
9 (XMLTYPE
10 (BFILENAME ('CAR_DIR', 'cars.xml'),
11 NLS_CHARSET_ID ('WE8MSWIN1252')),
12 '/CARS/CAR')))
13 /
5 rows created.
SCOTT@orcl_11g> SELECT * FROM car
2 /
CAR_ID MODEL MAKE YEAR
---------- --------------- --------------- ----------
1000 Accord Honda 2004
1001 Accord Civic 2002
1002 Charger Dodge 2006
1003 Mustang Ford 1965
1004 Camaro Chevrolet 1969
5 rows selected.
SCOTT@orcl_11g>
|
|
|
|
Goto Forum:
Current Time: Thu Apr 18 00:58:20 CDT 2024
|
] 
Blog
Search
Help
Members
Register
Login
Home
