Hi Guys,
I have this xml file (see attached). The idea is that using a normal oracle table (no xmltype datatype field), I want to load the record(s)
on the xml file to this table but ONLY records for <OppfLegemiddelMerkevare>, and ONLY extract 3 elements (or whatever is that called) from this namely:
example:
<Id>ID_C55B1496-8342-43DA-96B4-0503D56BE035</Id> as CODE
<Varenavn>Klorhexidin SA</Varenavn> as NAME
<NavnFormStyrke>Klorhexidin SA Øyedr 0,2 mg/ml</NavnFormStyrke> as DESCRIPTION
the rest are irrelevant.
Structure of my oracle table is:
MY_ORACLE_TBL
--------------------
CODE VARCHAR2(50)
NAME VARCHAR2(100)
DESCRIPTION VARCHAR2(2000)
What I have so far is this now based on my reseach (google):
insert into MY_ORACLE_TBL (CODE, NAME, DESCRIPTION)
select *
from xmltable('/KatLegemiddelMerkevare/OppfLegemiddelMerkevare'
passing xmltype(bfilename('XML_DIR', 'mymxl.xml'), nls_charset_id('CHAR_CS'))
columns CODE varchar2(50) path 'Id',
NAME varchar2(100) path 'Varenavn',
DESCRIPTION varchar2(2000) path 'NavnFormStyrke');
but this always gives me an error:
SQL Error: ORA-00942: table or view does not exist
ORA-06512: at "SYS.DBMS_LOB", line 523
ORA-06512: at "SYS.XMLTYPE", line 287
ORA-06512: at line 1
00942. 00000 - "table or view does not exist"
100% sure XML_DIR is created on the server running oracle. The xml file as well is in there.
Please help.
TIA,
Yorge
I have this xml file (see attached). The idea is that using a normal oracle table (no xmltype datatype field), I want to load the record(s)
on the xml file to this table but ONLY records for <OppfLegemiddelMerkevare>, and ONLY extract 3 elements (or whatever is that called) from this namely:
example:
<Id>ID_C55B1496-8342-43DA-96B4-0503D56BE035</Id> as CODE
<Varenavn>Klorhexidin SA</Varenavn> as NAME
<NavnFormStyrke>Klorhexidin SA Øyedr 0,2 mg/ml</NavnFormStyrke> as DESCRIPTION
the rest are irrelevant.
Structure of my oracle table is:
MY_ORACLE_TBL
--------------------
CODE VARCHAR2(50)
NAME VARCHAR2(100)
DESCRIPTION VARCHAR2(2000)
What I have so far is this now based on my reseach (google):
insert into MY_ORACLE_TBL (CODE, NAME, DESCRIPTION)
select *
from xmltable('/KatLegemiddelMerkevare/OppfLegemiddelMerkevare'
passing xmltype(bfilename('XML_DIR', 'mymxl.xml'), nls_charset_id('CHAR_CS'))
columns CODE varchar2(50) path 'Id',
NAME varchar2(100) path 'Varenavn',
DESCRIPTION varchar2(2000) path 'NavnFormStyrke');
but this always gives me an error:
SQL Error: ORA-00942: table or view does not exist
ORA-06512: at "SYS.DBMS_LOB", line 523
ORA-06512: at "SYS.XMLTYPE", line 287
ORA-06512: at line 1
00942. 00000 - "table or view does not exist"
100% sure XML_DIR is created on the server running oracle. The xml file as well is in there.
Please help.
TIA,
Yorge