Import/load XML file (record) to a normal table using Oracle 10g 1

[yorge]

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

 

[taupirho]

This looks like a permission problem. Check which Oracle user created the directory and ensure they have granted permissions to read this directory to other users as required.

In order to understand recursion, you must first understand recursion.

 

[yorge]

hello taupirho, tnx for the reply...grant fix it...the error is gone now but after execution, there are 0 rows inserted :-(
can you please take a look at the code I posted and see what's wrong with it?

 

[yorge]

it should be from here: /KatLegemiddelMerkevare/OppfLegemiddelMerkevare/LegemiddelMerkevare

but im getting 0 records

 

[taupirho]

You have two ID nodes, one hanging off the /KatLegemiddelMerkevare/OppfLegemiddelMerkevare node and one off the /KatLegemiddelMerkevare/OppfLegemiddelMerkevare/LegemiddelMerkevare node. Which one are you trying to get ? Also both Varenavn and NavnFormStyrke are off the /KatLegemiddelMerkevare/OppfLegemiddelMerkevare/LegemiddelMerkevare nodes.

So I would say you need to amend your select statement to ensure that you get the correct pathnames of each of these nodes. Something like:-

nsert 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', ***** (or maybe 'LegemiddelMerkevare/Id' ********
NAME varchar2(100) path 'LegemiddelMerkevare/Varenavn',
DESCRIPTION varchar2(2000) path 'LegemiddelMerkevare/NavnFormStyrke');

In order to understand recursion, you must first understand recursion.

 

[yorge]

hi taupirho, tried both pathnames but still zero rows inserted.
I tested for the ID only with this code:

insert into MY_ORACLE_TBL (CODE)
select *
from xmltable('/KatLegemiddelMerkevare/OppfLegemiddelMerkevare/LegemiddelMerkevare'
passing xmltype(bfilename('XML_DIR', 'myxml.xml'), nls_charset_id('CHAR_CS'))
columns Id varchar2(50) path 'Id'
);

0 rows inserted.

Is it possible for you to try this from your end using the xml file I attached?

TIA,
Yorge

 

[taupirho]

I think the issue is with the format of your XML. Consider this.

$ type myxml.xml
<KatLegemiddelMerkevare>
<OppfLegemiddelMerkevare>
<Id>ID_DF169A83-8C7D-4349-BD54-32DB56330CE0</Id>
<Tidspunkt>2013-08-29T12:47:06</Tidspunkt>
<Status V="A" DN="Aktiv oppføring" />
</OppfLegemiddelMerkevare>
</KatLegemiddelMerkevare>


SQL> l
1 SELECT
2 a.id
3 FROM
4 xmltable
5 (
6 'KatLegemiddelMerkevare'
7 PASSING
8 SYS.XMLType
9 (
10 bfilename('ISDADIR', 'myXML.xml'),
11 nls_charset_id('CHAR_CS')
12 )
13 COLUMNS id varchar2(50) PATH 'OppfLegemiddelMerkevare/Id'
14* ) a
SQL> /

ID
--------------------------------------------------
ID_DF169A83-8C7D-4349-BD54-32DB56330CE0




In order to understand recursion, you must first understand recursion.

 

[yorge]

hmmmmm, the file I attached is a copy from a client. Is it safe to say that the xml file is erroneous or something?

 

[taupirho]

Perhaps, its not well formed or something. I don't know. If I were you I would take my little sample XML file and add to it gradually until it becomes what you originally had and see how far you get before it breaks. I assume you managed to get the little example snippet I gave you to work ?

In order to understand recursion, you must first understand recursion.

 

[yorge]

yup, I did...I got it to work using your sample...tnx