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 Go to next message
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 Go to previous messageGo to next message
Barbara Boehmer
Messages: 7930
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 #324737 is a reply to message #324509] Tue, 03 June 2008 17:43 Go to previous messageGo to next message
lupeg
Messages: 5
Registered: June 2008
Junior Member
Beautiful!!! That worked perfectly. Thank you very much!!
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 Go to previous message
Barbara Boehmer
Messages: 7930
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>

Previous Topic: XMLP REPORT RUN SLOWLY
Next Topic: EXTRACTVALUE MULTIPLE NODES
Goto Forum:
  


Current Time: Tue Jul 22 15:37:16 CDT 2014

Total time taken to generate the page: 0.07086 seconds